Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
D_PBI
Post Patron
Post Patron

How to filter a set of rows based on two overlapping filters? (more complicated than title suggests)

Hi
Please view the below screenshot of the data model to work with.

D_PBI_1-1695380680578.png


There are two tables Parent and Child. The join between them is based on the RecordID field. It is (should be) a 1-* cardinality.
The report will contain a table visual that includes fields from both the Parent and Child tables. A reason why I haven't merged the Child table into the Parent table is due to another page, in the same report, showing Child fields in a standalone manner, and not every Child record has a Parent record (bad data - see RecordID 7 for an example).

If I were to drag the fields from both tables, with the Parent table being the driver for this visual, then the table would look like the below:

D_PBI_6-1695382855440.png

 

I would like your help on the following.
The user should have a slicer to show either all records (so the all the records shown in the immediate above table). Or to show the relevant records which are those records that have an entry in both the Parent and Child tables (i.e. RecordID field present), and have a date in the specified timeframe. This specified timeframe will be set by a relative date slicer on the Date field.
Note: when I say all or relevant records, these records may still be filtered by any other slicers on the page. We just need to factor that into any DAX.

I'm thinking I need to 'enter data' to create a table to use as the slicer's contents, something like the below:

D_PBI_3-1695381608960.png


I will also need a date filter to allow the user to select the relative date, like the below:

D_PBI_4-1695381718546.png

Taking the dummy table (shown again, below). If the user chose 'All records' then all the rows below will be returned (barring no other slicer has been configured - i.e. a user selects Type = Temp, this would reduce the rows shown just to the last two).

D_PBI_6-1695382855440.png

 


If the user was to select 'Relevant records' and the relative date slicer was set to the next 12 months (always starting from day the report is used (i.e. TODAY() ) which is currently the 22/09/23) then I'd want to show the RecordIDs that fulfil those conditions. The expected rows shown would be:

D_PBI_5-1695382189624.png

Notice how at least one Child record, per RecordID, has a Date that falls in the next 12 months (from today being the 22/09/23). As this condition is met, I'd want all records with those RecordIDs to be shown regardless if the other Child records, for that RecordID, fall in the next 12 months or not. Again, these returned rows are subject to any other slicers being configured.

I hope this all makes sense. If you can help me achieve this, it will be much appreacited. Thanks.

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @D_PBI ,

 

Here's the result. Hope that helps.

vstephenmsft_0-1695619896445.png

vstephenmsft_1-1695619904350.png

 

You can check my attachment for more details.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @D_PBI ,

 

Here's the result. Hope that helps.

vstephenmsft_0-1695619896445.png

vstephenmsft_1-1695619904350.png

 

You can check my attachment for more details.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

@v-stephen-msft - It works as needed. I really appreciate your help with this. Thanks very much.

D_PBI
Post Patron
Post Patron

Bump. Is anybody able to advise? Thanks.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.