Forum Discussion
Make the measures dynamic with date range selection.
I am working on a Dahsboard and I have created 3 measues that calcuate the
inactive clients within 2 years, in active client beyond 2 years, and cross service clients (meaning the number of client who have mutliple projects in differnet service line. I will write the measure below
Also, I have a dete range slicer as filter in the top of the dashboard, the probelm is that the measures are not dynamic with the selected date range ans showed a fixed number that consider all the data source without considering the date range filter
how i can make these measres dynmaic with any date range selection
here is the measures:
Hi Anonymous
For your question, in your dax I see that you used "ALL" for filtering.
In fact, "ALL" removes the effect of the slicer. You can modify "ALL" to "ALLSELECTED".
"ALLSELECTED" retains the slicer filter and removes the other filters.
Your code should look like this:
InactiveClients_Beyond2Years = CALCULATE( COUNTROWS( FILTER( VALUES(SQL_Database_Website[NTID]), CALCULATE(MAX(SQL_Database_Website[SubmitDate])) <= MAX(SQL_Database_Website[SubmitDate]) - 730 ) ), ALLSELECTED(SQL_Database_Website[SubmitDate]) -- modify "ALL" to "ALLSELECTED" ) InactiveClients_Within2Years = CALCULATE( COUNTROWS( FILTER( VALUES(SQL_Database_Website[NTID]), CALCULATE(MAX(SQL_Database_Website[SubmitDate])) > MAX(SQL_Database_Website[SubmitDate]) - 730 && CALCULATE(MAX(SQL_Database_Website[SubmitDate])) <= MAX(SQL_Database_Website[SubmitDate]) ) ), ALLSELECTED(SQL_Database_Website[SubmitDate]) ) CrossServiceClients = CALCULATE( COUNTROWS( FILTER( VALUES(SQL_Database_Website[NTID]), CALCULATE(DISTINCTCOUNT(SQL_Database_Website[Type])) > 1 ) ), ALLSELECTED(SQL_Database_Website[SubmitDate]) )Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
For your question, in your dax I see that you used "ALL" for filtering.
In fact, "ALL" removes the effect of the slicer. You can modify "ALL" to "ALLSELECTED".
"ALLSELECTED" retains the slicer filter and removes the other filters.
Your code should look like this:
InactiveClients_Beyond2Years = CALCULATE( COUNTROWS( FILTER( VALUES(SQL_Database_Website[NTID]), CALCULATE(MAX(SQL_Database_Website[SubmitDate])) <= MAX(SQL_Database_Website[SubmitDate]) - 730 ) ), ALLSELECTED(SQL_Database_Website[SubmitDate]) -- modify "ALL" to "ALLSELECTED" ) InactiveClients_Within2Years = CALCULATE( COUNTROWS( FILTER( VALUES(SQL_Database_Website[NTID]), CALCULATE(MAX(SQL_Database_Website[SubmitDate])) > MAX(SQL_Database_Website[SubmitDate]) - 730 && CALCULATE(MAX(SQL_Database_Website[SubmitDate])) <= MAX(SQL_Database_Website[SubmitDate]) ) ), ALLSELECTED(SQL_Database_Website[SubmitDate]) ) CrossServiceClients = CALCULATE( COUNTROWS( FILTER( VALUES(SQL_Database_Website[NTID]), CALCULATE(DISTINCTCOUNT(SQL_Database_Website[Type])) > 1 ) ), ALLSELECTED(SQL_Database_Website[SubmitDate]) )Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.