March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi everyone,
I am building up PBI report for my boss (if this task can't get done, I am afraid I would get fired 😅). There's a one dataset A which includes DATE column and ID column inside. The task is I have to build 2 slicers of date ranges using the same DATE column to filter the same ID column in the dataset A and showing a table of all IDs that remove all common IDs between 2 date ranges. I hope you all get what I mean.
My solution is to doubling the date table and the ID table then link the each date table with each ID table. But the problem is now I don't know how to compare the 2 tables to show the common IDs generated when filtering 2 date ranges. Please help me with this issue. If you have other better idea on how to work out it, please guide me through. Thousands thanks to you all!
Solved! Go to Solution.
Thank you @v-kkf-msft for your support, I have made my own solution. I guess you just don't get what I mean but it's alright. I duplicated the main table twice then use the date in both date in those tables for making slicers. So they don't conflict to each other when filtering. I use one table with unique ID to filter the other 2 duplicated tables.
And the solution loooks like that
Hi @CamelliaNguyen ,
Please create the following inactive relationship.
Then create the measure to show the items in the visual when it is not blank.
Measure =
VAR Date1_filter =
CALCULATE (
COUNTROWS ( 'Table' ),
USERELATIONSHIP ( 'Date 1'[Date], 'Table'[Date] )
)
VAR Date2_filter =
CALCULATE (
COUNTROWS ( 'Table' ),
USERELATIONSHIP ( 'Date 2'[Date], 'Table'[Date] )
)
RETURN
IF ( Date1_filter + Date2_filter > 0, 1 )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @v-kkf-msft for your support, I have made my own solution. I guess you just don't get what I mean but it's alright. I duplicated the main table twice then use the date in both date in those tables for making slicers. So they don't conflict to each other when filtering. I use one table with unique ID to filter the other 2 duplicated tables.
And the solution loooks like that
Hi tamej1, appreciate your support but it doesn't work the way I want it to be mate. I mean Im still unable to combine 2 tables after filtering 2 date ranges to show their different ID between them.
Hi @CamelliaNguyen
You can build a bridge table between the two fact tables that contains all unique ID's. Then use this table to filter both tables (One to many / One way) relationship. You can simply create a calculated table
ID's =
DISTINCT ( UNION ( VALUES ( Sheet1[ID1] ), VALUES ( Sheet2[ID2] ) ) )
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
89 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |