Using a variable to filter a table

[redhughes]

Hi,

I have a table with client names, their review dates and how many days till their next review:

Client	Review due	Days till next review	Booked
John Smith	20/02/2021	-12	1
Jane Smith	10/03/2021	5	1
Jean Smith	06/04/2021	32	0

What I would love to achieve (but not sure how) is a report page with a matrix visual, 3 cards and a slicer, and:

• as you change the slicer value (X that represents the number of days till the next review) both the matrix and the cards get updated; slicer could have a range of 0-180
• cards would show number of clients with a review in X number of days (i.e. less or equal), how many of them are booked and the %
• e.g. if the slicer value is 14 then only John and Jane would show, and cards would show 2 / 2 / 100%
• but if the slicer was set to 40, then all three clients whould show, and cards would show 3 / 2 / 66%
• attached is an example achieved with filters, but would prefer an independent variable as we have 2 types of reviews and would like to fit both in one page and add them up together (so we know how many reviews in total we have upcoming and how many of them are booked)

[v-cazheng-msft]

Hi, @redhughes 

You can consider SELECTEDVALUE function to get current value in Slicer and do calculations for the fields you want to add to Cards with the value from Slicer.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[redhughes]

Will this work for the table/matrix though?

[v-cazheng-msft]

Hi, @redhughes 

If you create Measures with the value from SELECTEDVALUE, then use these Measures to create table or matrix visual. Then you will get variable result according to the value of the Slicer.  You can refer SELECTEDVALUE Function DAX and Using the SELECTEDVALUE function in DAX.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[redhughes]

Hi @v-cazheng-msft, I've created a parameter called 'review-cutoff' and added a slicer to the page. then created a measure:

!reviews-new-light-within = if(sum(clients[!days-till-next-light-review-value])<=SELECTEDVALUE('Review-cutoff'[Review-cutoff]),if(sum(clients[!days-till-next-light-review-value])>=0,1,0),0)

this works well - every client has that measure in the table and the table contents change as I move the slicer (see attachment).

however, I'm now struggling to create the measure that will count how many clients are within this cutoff. I've tried this:

Measure = calculate(countrows(clients),[!reviews-new-light-within]>0)

Measure = calculate(COUNTA(clients[!days-till-next-light-review-value]),[!reviews-new-light-within]>0)

Measure = calculate(counta(clients[!days-till-next-light-review-value]),clients[!days-till-next-light-review-value]>=0,clients[!days-till-next-light-review-value]<=selectedvalue('Review-cutoff'[Review-cutoff]))+0

but it keeps telling me "A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

What am I doing wrong? Thanks for your help!

[v-cazheng-msft · Accepted solution]

Hi, @redhughes 

If you want use SELECTEDVALUE function as a filter in CALCULATE function, you need put it in FILTER function firstly. You can make some changes to the third Measure.

sure =

CALCULATE (

    COUNTA ( clients[!days-till-next-light-review-value] ),

    clients[!days-till-next-light-review-value] >= 0,

    FILTER (

        'clients',

        clients[!days-till-next-light-review-value]

            <= SELECTEDVALUE ( 'Review-cutoff'[Review-cutoff] )

    )

) + 0

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.