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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
CamelliaNguyen
Frequent Visitor

Comparing results from filtering 2 date range filters in the same Dataset

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!

Screenshot 2022-07-01 113308.pngScreenshot 2022-07-01 113956.png

 

1 ACCEPTED 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. Screenshot 2022-07-06 081846.png

And the solution loooks like thatScreenshot 2022-07-06 082140.png

View solution in original post

4 REPLIES 4
v-kkf-msft
Community Support
Community Support

Hi @CamelliaNguyen ,

 

Please create the following inactive relationship.

 

vkkfmsft_0-1657008761361.png

 

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 )

vkkfmsft_1-1657008932782.png

 

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. Screenshot 2022-07-06 081846.png

And the solution loooks like thatScreenshot 2022-07-06 082140.png

CamelliaNguyen
Frequent Visitor

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. 

Screenshot 2022-07-04 150221.png

tamerj1
Super User
Super User

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] ) ) )

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.