powerbinote.txt

there are six basic charts in powerbi 
1 column chart 
2 stacked column chart
3 pie chart
4 donut chart
5 funnel chart
6 ribbon chart
7 keep only and exlude => this is to filter data 
8 view data and export => this is to export or import data 
in powerbi we can import data from 99+ data source in 

to load certain data we can go to get data from above bar and than we can load data from where ever we want to get just select the data if its there in your system and then click on load the data we can visulize the data there is 2 thing x axis and y axis where we can select the data to visulize the data there are more than 30 + charts availabel till now 
one thing here is column chart and stacked column chart are almost similar
there are different ways to visulize the data and to format it to look good we just need to go to format and change according to our need and we want to show the client


the include and exclude of data is to compare ot to filter some data for client to see we can use this feature by going to focus mode and using ctrl button we can select all the values we want to compare by again clicking on right button of mouse and than click on include to compare many values and to remove from certain chart we can use exclude to do so.

to export data from powerbi just make a chart and than go to focus mode and than select a data what you want and than right click on mouse and than select show data point as table and than you can see export option there by default it will export data in csv format 

to create a map in power bi we can select the map from visulaization bar and it will give us map and than we can see sales and other aspect of it One thing which is very important here is if the the state or column or whatever you have selected is in different form just select it will take you to column tools and from there select data category and select which type of column is present there 

here tooltip is nothing but when you hover on mouse and it will show you the detail of the thing you want 

to create map with pie chart we need to select map first and than select whatever filled we want in the different things and than you can see map with pie chart

to format the map just go to format and you can see there are various options to format various thing of map using it  there is also theme style availabel for map in powerbi use it to customize it 

to create any map of country you just need to have state name or something like that 

you can manually enter data using enter data from navbar in powerbi and use it 

Table and matrix in powerbi 

to create a table simply import the data and than select table and drag and drop no of column you want to add 
conditional formatting is very important in our table formatting it will you to understand data more clearly 

to work on aggregation there is lot of things we can use it to do so 

to create a matrix in powerbi we need to take care of 3 things that are rows columns and values what we want 

how to make filter 
hirearchies is nothing but sub category of something
total and subtotal are all also there in format 
we can also change the number format in powerbi from numbers to thousands and other things as well
scatter plot is only the chart which have animation in it and for that what we need to do is make have date column and than we can play if we want to compare all data just press ctrl and select different point which you want to compare and you can go for it 


gauge chart is useful if we want to target field with it 
to make a target we can go to home and than new meausures and make a new target 


there is a thing which is drill through which we can use it make different pages connector together



dax in powerbi is very useful in (data analysis exploration)
Maths & Statistical Functions
SUM(<column>) Adds all the numbers in a column.
SUMX(<table>, <expression>) Returns the sum of an expression evaluated for each row in a table.
AVERAGE(<column>) Returns the average (arithmetic mean) of all the numbers in a column.
AVERAGEX(<table>, <expression>) Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.
MEDIAN(<column>) Returns the median of a column.
MEDIANX(<table>, <expression>) Calculates the median of a set of expressions evaluated over a table.
GEOMEAN(<column>) Calculates the geometric mean of a column.
GEOMEANX(<table>, <expression>) Calculates the geometric mean of a set of expressions evaluated over a table.
COUNT(<column>) Returns the number of cells in a column that contains non-blank values.
COUNTX(<table>, <expression>) Counts the number of rows from an expression that evaluates to a non-blank value.
DIVIDE(<numerator>, <denominator> [,<alternateresult>]) Performs division and returns alternate result or BLANK() on division by 0.
MIN(<column>) Returns a minimum value of a column.
MAX(<column>) Returns a maximum value of a column.
COUNTROWS([<table>]) Counts the number of rows in a table.
DISTINCTCOUNT(<column>) Counts the number of distinct values in a column.
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) Returns the ranking of a number in a list of numbers for each row in the table argument.
Filter Functions
FILTER(<table>, <filter>) Returns a table that is a subset of another table or expression.
CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]]) Evaluates an expression in a filter context.
HASONEVALUE(<columnName>) Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise, it is FALSE.
ALLNOBLANKROW(<table> | <column>[, <column>[, <column>[,…]]]) Returns a table that is a subset of another table or expression.
ALL([<table> | <column>[, <column>[, <column>[,…]]]]) Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
ALLEXCEPT(<table>, <column>[, <column>[,..]]) Returns all the rows in a table except for those rows that are affected by the specified column filters.
REMOVEFILTERS([<table> | <column>][, <column>[, <column>[,…]]]]) Clear all filters from designated tables or columns.
Logical Functions
IF(<logical_test>, <value_if_true>[, <value_if_false>]) Checks a condition, and returns a certain value depending on whether it is true or false.
AND(<logical 1>, <logical 2>) Checks whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise, it returns FALSE.
OR(<logical 1>, <logical 2>) Checks whether one of the arguments is TRUE to return TRUE. The function returns FALSE if both arguments are FALSE.
NOT(<logical>) Changes TRUE to FALSE and vice versa.
SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>]) Evaluates an expression against a list of values and returns one of possible results
IFERROR(<value>, <value_if_error>) Returns value_if_error if the first expression is an error and the value of the expression itself otherwise.
Date & Time Functions
CALENDAR(<start_date>, <end_date>) Returns a table with a single column named "Date" that contains a contiguous set of dates.
DATE(<year>, <month>, <day>) Returns the specified date in datetime format.
DATEDIFF(<date_1>, <date_2>, <interval>) Returns the number of units between two dates as defined in <interval>.
DATEVALUE(<date_text>) Converts a date in text to a date in datetime format.
DAY(<date>) Returns a number from 1 to 31 representing the day of the month.
WEEKNUM(<date>) Returns weeknumber in the year.
MONTH(<date>) Returns a number from 1 to 12 representing a month.
QUARTER(<date>) Returns a number from 1 to 4 representing a quarter.
Time Intelligence Functions
DATEADD(<dates>, <number_of_intervals>, <interval>) Moves a date by a specific interval.
DATESBETWEEN(<dates>, <date_1>, <date_2>) Returns the dates between specified dates.
TOTALYTD(<expression>, <dates>[, <filter>][, <year_end_date>]) Evaluates the year-to-date value of the expression in the current context.
SAMEPERIODLASTYEAR(<dates>) Returns a table that contains a column of dates shifted one year back in time.
STARTOFMONTH(<dates>) // ENDOFMONTH(<dates>) Returns the start // end of the month.
STARTOFQUARTER(<dates>) // ENDOFQUARTER(<dates>) Returns the start // end of the quarter.
STARTOFYEAR(<dates>) // ENDOFYEAR(<dates>) Returns the start // end of the quarter.
Relationship Functions
CROSSFILTER(<left_column>, <right_column>, <crossfiltertype>) Specifies the cross-filtering direction to be used in a calculation.
RELATED(<column>) Returns a related value from another table.
Table Manipulation Functions
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) Returns a summary table for the requested totals over a set of groups.
DISTINCT(<table>) Returns a table by removing duplicate rows from another table or expression.
ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…) Adds calculated columns to the given table or table expression.
SELECTCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…) Selects calculated columns from the given table or table expression.
GROUPBY(<table> [, <groupBy_columnName>[, [<column_name>] [<expression>]]…) Create a summary of the input table grouped by specific columns.
INTERSECT(<left_table>, <right_table>) Returns the rows of the left-side table that appear in the right-side table.
NATURALINNERJOIN(<left_table>, <right_table>) Joins two tables using an inner join.
NATURALLEFTOUTERJOIN(<left_table>, <right_table>) Joins two tables using a left outer join.
UNION(<table>, <table>[, <table> [,…]]) Returns the union of tables with matching columns.
Text Functions
EXACT(<text_1>, <text_2>) Checks if two strings are identical (EXACT() is case sensitive).
FIND(<text_tofind>, <in_text>) Returns the starting position a text within another text (FIND() is case sensitive).
FORMAT(<value>, <format>) Converts a value to a text in the specified number format.
LEFT(<text>, <num_chars>) Returns the number of characters from the start of a string.
RIGHT(<text>, <num_chars>) Returns the number of characters from the end of a string.
LEN(<text>) Returns the number of characters in a string of text.
LOWER(<text>) Converts all letters in a string to lowercase.
UPPER(<text>) Converts all letters in a string to uppercase.
TRIM(<text>) Remove all spaces from a text string.
CONCATENATE(<text_1>, <text_2>) Joins two strings together into one string.
SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>) Replaces existing text with new text in a string.
REPLACE(<old_text>, <start_posotion>, <num_chars>, <new_text>) Replaces part of a string with a new string.

Information Functions
COLUMNSTATISTICS() Returns statistics regarding every column in every table. This function has no arguments.
NAMEOF(<value>) Returns the column or measure name of a value.
ISBLANK(<value>) // ISERROR(<value>) Returns whether the value is blank // an error.
ISLOGICAL(<value>) Checks whether a value is logical or not.
ISNUMBER(<value>) Checks whether a value is a number or not.
ISFILTERED(<table> | <column>) Returns true when there are direct filters on a column.
ISCROSSFILTERED(<table> | <column>) Returns true when there are crossfilters on a column.
USERPRINCIPALNAME() Returns the user principal name or email address. This function has no arguments.
DAX Statements
VAR(<name> = <expression>) Stores the result of an expression as a named variable. To return the variable, use RETURN after the variable is defined.
COLUMN(<table>[<column>] = <expression>) Stores the result of an expression as a column in a table.
ORDER BY(<table>[<column>]) Defines the sort order of a column. Every column can be sorted in ascending (ASC) or descending (DESC) way.
DAX Operators
Comparison operators	Meaning
=	Equal to
= =	Strict equal to 
>	Great than
<	Smaller than
> =	Greater than or equal to
= <	Smaller than or equal to
< >	Not equal to 
Text operator	Meaning	Example
&	Concatenates text values	Concatenates text values | [City]&", "&[State]
Logical operator	Meaning	Example
&&	AND condition	([City] = "Bru") && ([Return] = "Yes"))
||	OR condition	([City] = "Bru") || ([Return] = "Yes"))
IN {} 	OR condition for each row	Product[Color] IN {"Red", "Blue", "Gold"}