Forum Discussion
Solution to turn a hard coded DAX Table OR filter into one which works with a Date Slicer
Hi
Thank you for all your suggestons, this has been really bothering me for ages but I have eventually found the solution: Sorry, for long winded post but based on my difficult experience of trying to find a solution, based on my limited understanding rather than anything else, I wanted to provide a comprehensive response.
Each post above has contributed to my better understandig of the issue, thank youu.
The main reasons why the following DAX code works:
1. The slicer calendar table 'CAL' has to be unrelated to the main 'ICC' table
2. It only works as a new Mesaure rather than a new Table.
3. The Measure can then be used for the Visual filter >=1
'CAL' is a calendar table and 'ICC' is the Main Table - whilst both tables are unrelated they do have a common text field which relate to a date. MonthY is a text field which has combined the completion month and year (this occurs in Transform Data) - this means it's not formatted as date field. The equivalent field in 'ICC' is CMonthY. This captures any completed cases where the Date Completed occurs within the selected month plus the ongoing, live cases:
New_Filter_Measure =
VAR Q_SELECT = SELECTEDVALUE('Cal'[MonthY])
RETURN
CALCULATE(
COUNTROWS('ICC'),
FILTER(
'ICC',
'ICC'[CMONTHY] = Q_SELECT || ('ICC'[CMONTHY]=""
))