Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
This is my scenario:
I have 1 fact table [Fact] with 2 dates and 2 calendars, [CalendarA] and [CalendarB]. I have both calendars with active relationships with the facts table.
One date is [Date A] and the other one is [Date B].
I have two tables in one I use these two formulas:
Measure A = CALCULATE( DISTINCTCOUNT( TableA[Number] ); CalendarA[DateA] ) Measure A LY = CALCULATE( [MeasureA]; SAMEPERIODLASTYEAR( TableA[DateA].[Date] ) )
And in the other table I use these 2 formulas
Measure B = CALCULATE( DISTINCTCOUNT( TableB[Number] ); CalendarB[DateB] ) Measure B LY = CALCULATE( [MeasureB]; SAMEPERIODLASTYEAR( TableB[DateB].[Date] ) )
Now, my problem is that I want to filter by CalendarA both tables but the filter only works for the first table visual and not the second one.
Maybe my approach is incorrect.
Any ideas?
Thank you in andvance
Solved! Go to Solution.
Hi @luxpbi
You may use USERELATIONSHIP Function.Please check attached file.
Reference:https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Measure B = CALCULATE( DISTINCTCOUNT( TableA[Booking number]),USERELATIONSHIP(CalendarA[DateA],TableA[DateB]))
Measure B LY = CALCULATE( [Measure B], SAMEPERIODLASTYEAR( CalendarA[DateA].[Date]))
Regards,
Hi @luxpbi
It is very hard to provide an accurate solution without looking at sample data.Please explain more about your expected output.Could you upload the .pbix file to OneDrive and post the link here? Do mask sensitive data before uploading.
How to Get Your Question Answered Quickly
Regards,
Hi @v-cherch-msft,
Here is some dummy data:
DateA | DateB | Booking number |
02/01/2019 | 02/01/2019 | 1 |
03/01/2019 | 03/01/2019 | 2 |
03/01/2019 | 03/01/2019 | 1 |
02/01/2019 | 02/01/2019 | 3 |
02/01/2019 | 02/01/2019 | 4 |
04/01/2019 | 04/01/2019 | 5 |
02/01/2019 | 02/01/2019 | 6 |
02/01/2019 | 31/12/2018 | 7 |
02/01/2019 | 31/12/2018 | 8 |
04/01/2019 | 10/06/2019 | 9 |
04/01/2019 | 07/01/2019 | 10 |
02/01/2019 | 03/01/2019 | 11 |
02/01/2019 | 03/01/2019 | 12 |
03/01/2019 | 02/02/2019 | 13 |
03/01/2019 | 07/01/2019 | 14 |
03/01/2019 | 02/02/2019 | 15 |
03/01/2019 | 02/02/2019 | 16 |
04/01/2019 | 05/01/2019 | 17 |
04/01/2019 | 05/01/2019 | 18 |
05/01/2019 | 02/02/2019 | 19 |
04/01/2018 | 04/01/2018 | 20 |
02/01/2018 | 02/01/2018 | 21 |
03/01/2018 | 03/01/2018 | 22 |
04/01/2018 | 04/01/2018 | 23 |
04/01/2018 | 04/01/2018 | 24 |
02/01/2018 | 02/01/2018 | 25 |
04/01/2018 | 04/01/2018 | 26 |
02/01/2018 | 02/01/2018 | 27 |
05/01/2018 | 05/01/2018 | 28 |
03/01/2018 | 03/01/2018 | 29 |
02/01/2018 | 02/01/2018 | 30 |
04/01/2018 | 04/01/2018 | 31 |
01/01/2018 | 01/01/2018 | 32 |
01/01/2018 | 01/01/2018 | 33 |
01/01/2018 | 01/01/2018 | 34 |
05/01/2018 | 05/01/2018 | 35 |
06/01/2018 | 31/12/2018 | 36 |
01/01/2018 | 01/01/2018 | 37 |
01/01/2018 | 01/01/2018 | 38 |
06/01/2018 | 10/06/2018 | 39 |
04/01/2018 | 04/01/2018 | 40 |
04/01/2018 | 04/01/2018 | 41 |
06/01/2018 | 06/01/2018 | 42 |
03/01/2018 | 03/01/2018 | 43 |
03/01/2018 | 07/01/2018 | 44 |
I want to filter by DateA and see Booking number LY by DateA and Booking number LY by DateB
Thank you for your help.
Hi @luxpbi
You may link the tableA and tableB with calendar A.Then create the measures.
Measure A = CALCULATE( DISTINCTCOUNT( TableA[Booking number]), TableA[DateA] )
Measure A LY = CALCULATE( [Measure A], SAMEPERIODLASTYEAR( CalendarA[DateA].[Date] ))
Regards
Hi @v-cherch-msft,
First of all I would like to thank to for your speed.
I don't quite understand your answer, do i have to create the Table B, that is equal to Table A?
In my model I don't have 2 Fact tables, I only have 1.
Thank you !
Hi @luxpbi
You may use USERELATIONSHIP Function.Please check attached file.
Reference:https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Measure B = CALCULATE( DISTINCTCOUNT( TableA[Booking number]),USERELATIONSHIP(CalendarA[DateA],TableA[DateB]))
Measure B LY = CALCULATE( [Measure B], SAMEPERIODLASTYEAR( CalendarA[DateA].[Date]))
Regards,
Hi @v-cherch-msft ,
It works as expected, but I need the next scenario:
If I filter year everything works fine:
but if I filter Month I need to see all future dates for Measure B.
Hope you can help in this scenario.
Thank you a lot !! for your help 🙂
Hi @luxpbi
I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.
Regards,
One Calendar with multiple relationships where one is active and other inactive should work alongside with the DAX function USERELATIONSHIP() -
e.g.
Revenue by check-out date = CALCULATE([Amount], USERELATIONSHIP('date'[date], 'factTable'[check-out date]) )
where
Amount = SUM('factTable'[Amount])
and your calendar has active relationship between 'factTable'[check-in date] and 'date'[date] and INACTIVE relationship between 'factTable'[check-out date] and 'date'[date]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |