Table visual does not update accordingly

[jagostinhoCT]

Hello,

I am creating compiling some visuals for a dashboard that allows for quicker assessment of Skills Reviews status for staff accross 3 different offices.

For such, a measure was created to count the number of Out of Date Reviews. 

The count is correct as I manually checked on the Data Sources XLSX, but the Users List does not update accordingly.

What might I be missing here?

Use link to download pbix and data sources.

https://bigfilebox.chapmantaylor.com/lwt/25144-GaV6WIsVPbnKDz60j1fMJ0cV4

Thank you for your help.

[chrisu · Accepted solution]

This is happening because [Reviews out of Date] is a measure, which means it doesn't have a row context to cross filter the office/names table.  Notice that you can't use Reviews out of Date as a slicer because it is a measure.  So the only thing that filters the Offce/Names table is "Manchester".  

The way I can think to fix this would be to add a "Reviews out of Date Status" calculated column/table, which will require you to rework the table structure and/or the calculations that Reviews out of Date is based on.  Once you have a "Reviews out of Date Status" column/table (i.e. for every name you would have a status of "Current" or "Out of Date"), you can use that in the bar chart as the value (to get the count), legend (to enable interaction with the table), and visual-level filter (to only show those with the out of date status).  

[wouterzijlstra]

A simple way to do this is to make a visual level filter on your text block where 'reviews out of date' is greater then 0 (zero).

If you filter by clicking on a bar in your table it will only show the six persons with a review out of date. Good luck!

"Please click the Accept as Solution if this resolves your issue; if it did not resolve your issue, provide more information on what happened instead.".

[Nhallquist]

I think the reason why your user list isn't updating is because you are using a formula to count all the reviews for an office, and then you are subtracting the completed reviews, then plotting the Difference on the chart.  I think you need to write a DAX expression in the data that determines if a Review is out of date.  Then you can use that Data field to calculate your chart and also to filter your table.

Hope this helps.

Nate

[chrisu · Accepted solution]

This is happening because [Reviews out of Date] is a measure, which means it doesn't have a row context to cross filter the office/names table.  Notice that you can't use Reviews out of Date as a slicer because it is a measure.  So the only thing that filters the Offce/Names table is "Manchester".  

The way I can think to fix this would be to add a "Reviews out of Date Status" calculated column/table, which will require you to rework the table structure and/or the calculations that Reviews out of Date is based on.  Once you have a "Reviews out of Date Status" column/table (i.e. for every name you would have a status of "Current" or "Out of Date"), you can use that in the bar chart as the value (to get the count), legend (to enable interaction with the table), and visual-level filter (to only show those with the out of date status).  

[jagostinhoCT]

Thanks for the clarification.

I have created a new calculated column

ReviewsDue Date Mark = IF(Skills[Review Age]>90,"Out of Date","Recent")

Trying now to get the number of Reviews Out Of Date with the CALCULATE but surely I am missing the point with this function.

This renders an error that I cannot understand.

Number of Reviews Out of Date = CALCULATE(COUNT(Skills[ReviewsDue Date Mark],ALL(Skills[ReviewsDue Date Mark]="Out of Date")))

Too many arguments were passed to the COUNT function. The maximum argument count for the function is 1.

[Eric_Zhang · Accepted solution]

@jagostinhoCT

Check this expression.

Number of Reviews Out of Date = CALCULATE(COUNTA(Skills[ReviewsDue Date Mark]),Skills[ReviewsDue Date Mark]="Out of Date")

If you have any question, feel free to let me know.

[jagostinhoCT]

@Eric_Zhang

Many thanks.

It works! I can now get the list of users whose review is out of date just by clicking the graph.

For future reference, and consolidate my knowledge, for the CALCULATE function I need to use the same column reference for both Expression and Filter?

CALCULATE(COUNTA(Skills[ReviewsDue Date Mark]),Skills[ReviewsDue Date Mark]="Out of Date")

[Eric_Zhang]

@jagostinhoCT

The COUNTA function counts the number of cells in a column that are not empty.

The expression wrapped in COUNTA depends on what to count, you don't have to use the same column.