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!
I have to create a table comparison within Power BI. Here´s demo data for better understanding. I have one table which consists of a number of IDs for some timestamps:
I have to create a comparison for two timestamps, which shows, which IDs are recorded on one timestamp but not the other, and which IDs are recorded on both timestamps. For this I have created a calculated column which allows to filter:
Intersection =
VAR Date1 = DATE(2020,4,1)
VAR Date2 = DATE(2020,5,3)
VAR Table1 = FILTER(TestTable, [Date]=Date1)
VAR Table2 = FILTER(TestTable, [Date]=Date2)
return IF(AND([Date]<>Date1,[Date]<>Date2),"irrelevant",
IF(NOT(CONTAINS(Table1, [ID], [ID])), "not included in Date1",
IF(NOT(CONTAINS(Table2, [ID], [ID])), "not included in Date2",
"included in Date1 and Date2")))
This seems to work well, but I need to get the two timestamps as parameter, selectable by the user. The user should be able to select an existing timestamp, not to enter any date (instead of hard coded variables in line 2 and 3 above). How can this be done? Is there any more elegant way to do the comparison?
Attached (corrected link) you´ll find the pbix file. Any comments are appreciated.
Regards,
Thomas
Hi,
Perhaps the following is what you are looking for. This will check per code if the number of dates is equal to the number of selected dates, if so they appear in both (or more).
PresentInBoth =
var NumberOfDates = CALCULATE(DISTINCTCOUNT(Table1[Date]);all(Table1);ALLSELECTED(Table1[Date]))
return
CALCULATE(DISTINCTCOUNT(Table1[Date])) = NumberOfDates
Kind regards,
Steve.
Please accept as solution if this is what you are looking for.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi @stevedep,
thank you for this hint, but I could not manage to get a solution. It seems to me, that it is not possible to use this context, because either it filters the target table in a way not intended, or it does not work at all.
Regards,
Thomas.
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
44 | |
32 | |
30 | |
18 | |
17 |