cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to maintain report filters on relatedtable

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.

 SampleModel.PNG

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

3 REPLIES 3
Highlighted
Solution Sage
Solution Sage

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.

Highlighted

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,

 

Highlighted

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

Thanks,

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors