How to maintain report filters on relatedtable

[DominicBrien]

Hello, I have a hard adding an aggregated column as it doesn’t react to report filters. I tried many solutions and read about contexts I just cannot find the way to get the context I need.

My data looks like this.

 

I need to add a column to the Users table that would have the number of cases for each user.

I can quite easily get it using this DAX formula CountRowsRelatedCases = COUNTROWS(RELATEDTABLE('Cases')).

The problem is that I want to add a report level filter that will filter the date dimension and get the number of cases for a user in the selected date range.

I tried many of the filter functions without achieving the right result, I also changed the relations to enable bi-directional filtering..

The final result has to enable me to report on number of users who have x, y, z number of cases in the selected date range.

Thanks in advance.

Dominic

[v-xjiin-msft]

Hi @DominicBrien,

In your scenario, the number of cases for each user is calculated in a column. Right?

You should know that column is used for fix values. If you want to return the values based on filters. I would suggest you to try measure instead of column.

For more detailed suggestions. Please share us your pbix file with One Drive or Google Drive if possible. So that I can know your actual situation.

Thanks,
Xi Jin.

[DominicBrien]

Hello @v-xjiin-msft, did you get a chance to look at my sample file?

Thanks,

[DominicBrien]

Yes I was tying to add it as a column.

I prepared a sample file with 3 main tables Date, Users and cases I also added summarized tables that do get me the information I need.

But don't ajust to report filter on date.

I tried a summarized table that indludes the filter based on measures but it remains empty.

SampleFile

Thanks,