Forum Discussion
Dynamic Column calculation based on Slicer Selection
Calculated columns are initialized regardless of Slicers.
If you'd like to filter your data via the selected value in a slicer, instead of a calcualted column, use a measure as below to filter in DAX.
selectedValue = IF ( ISFILTERED ( FILTERTABLE[Value] ) && HASONEVALUE ( FILTERTABLE[Value] ), LASTNONBLANK ( FILTERTABLE[Value], 0 ), "a default value" )
Hi bkoo
The Accepted solution did work somewhat if I recall correctly but presented some other issues for me . Since then the Power BI
team have released date based slicers so the the following solution is somewhat redundant .
My main requirement was to essentially have a easy way to slice revenue data based on last latest month / last 12 months / last 24 months etc
Anyway what I did was I first created a simple table screenshot below (Just made one in excel and imported it)
Next I created a another table with the following dax
- Note this assumes you have a separate date table , In my case that was "DateKey" , and a column with all relavant dates called "date"
"DatePeriod =
UNION (ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('DateKey',DATESBETWEEN('DateKey'[Date],EOMONTH(MAX(DateKey[Date]),-1)+1,[Max Date])), 'DateKey'[Date]),"Period","Last Month"),ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('DateKey',DATESBETWEEN('DateKey'[Date],EOMONTH(MAX(DateKey[Date]),-12)+1,[Max Date])), 'DateKey'[Date]),"Period","Last 12 Months") , ADDCOLUMNS(SUMMARIZE( CALCULATETABLE('DateKey' , DATESBETWEEN(DateKey[Date],EOMONTH(MAX(DateKey[Date]),-24)+1,[Max Date])), 'DateKey'[Date]),"Period","Last 24 Months") , ADDCOLUMNS(SUMMARIZE( CALCULATETABLE('DateKey' , DATESBETWEEN(DateKey[Date],EOMONTH(MAX(DateKey[Date]),-36)+1,[Max Date])) , 'DateKey'[Date]),"Period","Last 36 Months"),ADDCOLUMNS(SUMMARIZE(CALCULATETABLE('DateKey'),'DateKey'[Date]),"Period","Overall")) "
Once done it should like the following
Form a relationship between the two tables .
Create a slicer based on Sales Period and you should be able to drive your relavant charts .
If this doesnt help in anyway , please give some further details and I will try to help
Hope this helps :)
Regards
Kam
Hi Kam,
I am somehow not able to get this to work. I have the Date table, also the DatePeriod table now. I joined Date to DatePeriod (1-many). And my Calls table to Date (many to one). My slicer is based on DatePeriod and shows the Last Month etc. fine. It even shows the corresponding (correct) data from Date table (in a table visual with just the Date and DatePeriod fields).
However, somehow my other visuals don't get that filter from slicer. Is it because the Calls are not directly related to the DatePeriod (they are connected via Date table). Thanks for your help.