Make the measures dynamic with date range selection.

[Anonymous]

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:

InactiveClients_Beyond2Years =

CALCULATE(

    COUNTROWS(

        FILTER(

            VALUES(SQL_Database_Website[NTID]),

            CALCULATE(MAX(SQL_Database_Website[SubmitDate])) <= MAX(SQL_Database_Website[SubmitDate]) - 730

        )

    ),

    ALL(SQL_Database_Website[SubmitDate])

)

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])

        )

    ),

    ALL(SQL_Database_Website[SubmitDate])

)

CrossServiceClients =

CALCULATE(

    COUNTROWS(

        FILTER(

            VALUES(SQL_Database_Website[NTID]),

            CALCULATE(DISTINCTCOUNT(SQL_Database_Website[Type])) > 1

        )

    ),

    ALL(SQL_Database_Website[SubmitDate])

)

[Anonymous · Accepted solution]

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.

[Anonymous]

Thanks a lot, it worked now and it's dynmaic 
I really appreciate your help