Tuesday, 1 October 2019

How to get the All Selected Values from a Slicer using DAX in Power BI

How to get the All Selected Values from a Slicer using SelectedValue, AllSelected, and ConcatenateX Functions in Power BI
Scenario :
Suppose we have a Slicer having the MonthNames created from the Table 'Calendar' MonthName Field.

Extracting single Selected Value from the above Slicer :
We can use the SELECTEDVALUE () Function to extract the single selection value from the above Slicer.
SelectMonth = SELECTEDVALUE('Calendar'[MonthName])

Result:


Extracting multiple Selected Values from the above Slicer :
Suppose if we wants to extract the multiple values selected in the Slicer, we can achieve this by using the combination of CONCATENATEX() and ALLSELECTED() Function as defined in the following..

MultiSelect = IF(ISFILTERED('Calendar'[MonthName]), CONCATENATEX( ALLSELECTED('Calendar'[MonthName]),'Calendar'[MonthName],","),"(All)")

Result:

Note : 
The above function will works for single selection as well.


-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

Wednesday, 11 September 2019

How to choose Custom refresh Date and Month based on the Current Date Scenario in Power BI

How to choose refresh Date as Last Friday date of Closed Week based on the Current Date and Week Day
Scenario :
If the Current Date falls on the Day "01", and Week Day falls either on {"Sat", "Sun", "Mon"}, then the report Refresh Date should be consider as the Last Fridays date.
This is one of the common Business scenario, where the user wants to see the refresh date of the Report as  closed(last) Month's , last Week-Friday date, when the user opens the report on the first day "01" of the Month.

We can achieve this in Power BI using DAX as follows :
Suppose the user opens report on "01-Sep-2019"
curDate = DATE(2019,09,01)

Since in the above Date, Day is "01" and it falls on "Sun", then as per the above scenario, the user will see the refresh Date as "30-Aug-2019" which is the last "Friday" date of the Last Week of the Previous Month, which is the actual reported Month.
refDate
VAR WkName=FORMAT(WEEKDAY([curDate]),"DDD")
Return
IF(DAY([curDate])=1&& WkName In {"Sat","Sun","Mon"},
    [curDate]-SWITCH(TRUE(),WkName="Sat",1,WkName="Sun",2,WkName="Mon",3),
    [curDate])

Based on the above Scenario based refresh date (refDate) we can calculate the CurMonth and the NxtMonth values as per our need.

CurMonthFY = FORMAT([refDate],"MMM")&"'"&RIGHT(YEAR([refDate]),2)

NxtMonthFY = FORMAT(([refDate]+INT(EOMONTH([refDate],0)-[refDate])+7),"MMM")&"'"&RIGHT((YEAR([refDate]+INT(EOMONTH([refDate],0)-[refDate])+7)),2)

Result:
Now look at the following Scenario, where if the Previous Month falls in Previous Year, as below..
curDate = DATE(2022,01,01)
Here the Day is "01" and Week is "Sat", so that based on the above Scenario, the result looks as follows..

Now look at another Scenario, where the CurDate is not meeting the above Scenario..
curDate = DATE(2019,11,01)
Here the Day is "01" but its Week is "Fri" which not meeting our Scenario. In this Case, the refresh Date will be same as the current Date.



Note :
You can develop your own logic based on the above Scenario.


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

Friday, 16 August 2019

How to use the Parent Child Hierarchy Functions in Power BI DAX

How to use Path(), PathLength(), PathItem() and PathItemReverse() Functions in Power BI
Power BI DAX provides the Path(), PathLength(), PathItem() and PathItemReverse() Functions to help users manage data that is presented as a Parent-Child Hierarchy in their models. 
With these functions a user can obtain the below information :
--the entire lineage of parents a row has, 
--how many levels has the lineage to the top parent, 
--who is the parent n-levels above the current row, 
--who is the n-descendant from the top of the current row hierarchy and is certain parent a parent in the current row hierarchy or not.

Now we will discuss about each function in detail based on the following data modal.
Let us suppose we have a Parent-Child Hierarchy as follows..
From the above Parent-Child hierarchy, we observe the following things.
The Child Id "111" does not have a Parent Id.
The Child Id "112" have a Parent Id as "112".
The Child Ids  "114,115,116" have the Parent Id as "113".
The Child Ids "117,118" have the Parent Id as "116"..etc.

PATH() function :
The Path() function returns a delimited text with the identifiers of all the Parents to the current row, starting with the oldest or top most to until current.
Syntax :
cPath = PATH('ParentChild'[Child_Id],'ParentChild'[Parent_Id])

Result:
PATHLENGTH() function :
The PathLength() function returns the number of levels in a given PATH(), starting at current level until the oldest or top most parent level.
In the following example column PathLength is defined as ‘=PATHLENGTH([cPath])’; the example includes all data from the Path() example to help understand how this function works.

Syntax :
cPathLen = PATHLENGTH('ParentChild'[cPath])

Result:
PATHITEM() function:
The PathItem() function returns the item at the specified position from a PATH() like result, counting from left to right. 
In the following example column PathItem - 4th from left is defined as ‘=PATHITEM([cPath], 4)’; this example returns the Child_Id at fourth position in the Path string from the left, using the same sample data from the Path() example.
Syntax:
cPathItem = PATHITEM('ParentChild'[cPath],4,INTEGER)

Result:

PATHITEMREVERSE() function:
The PathItemReverse function returns the item at position from a PATH() like function result, counting backwards from right to left.
In the following example column PathItemReverse - 3rd from right is defined as ‘=PATHITEMREVERSE(c[Path], 3)’; this example returns the Id at third position in the Path string from the right, using the same sample data from the Path() example.

Syntax:
cPathItemRev = PATHITEMREVERSE('ParentChild'[cPath],3,INTEGER)

Output:

PATHCONTAINS() function:
The PathContains function returns TRUE if the specified item exists within the specified path. In the following example column PathContains - Id 123 is defined as ‘=PATHCONTAINS([cPath], "123")’; this example returns TRUE if the given path contains Id 123. This example uses the results from the Path() example above.
Syntax:
cPathContains = PATHCONTAINS(ParentChild[cPath],"123")

Output:

Note :
The above functions are Column functions, so that I used the suffix "c" for each column that defined like "cPath".

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts