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.
I have a table with a date column that has multiple duplicate values. I want to have 2 slicers with dates and compare the similar records between those 2 dates. I'm creating 2 filtered versions of the initial table based on each slicer. The DAX that I'm using is the following:
Filtered_table1 = FILTER(Initial_table, Initial_table[Date] = SELECTEDVALUE(DateTable1[Date]))
Filtered_table2 = FILTER(Initial_table, Initial_table[Date] = SELECTEDVALUE(DateTable2[Date]))
Although the part: Initial_table[Date] = SELECTEDVALUE(DateTable1[Date]) doesn't work. I added a column to my initial table with that value and the result is always False, even though the 2 dates are exactly the same. I have doublechecked all the type matches and I can't find a solution online to work around this problem.
Solved! Go to Solution.
The basic principle would be to define a measure which can act as a flag to say whether a given row should be visible or not, and use that as a visual filter. Something along the lines of
Visible flag =
var table1 = FILTER( 'Table', some conditions )
var table2 = FILTER( 'Table', different conditions )
var currentValue = SELECTEDVALUE('Table'[Unique ID])
return IF( currentValue IN EXCEPT( table1, table2),1, 0)
then add the unique ID column to a table visual and add the measure as a filter to only show when the value is 1
Calculated columns and tables are only calculated during data load, so they don't pay any attention to filters or slicers.
If you need something dynamic to react to filters and slicers then it must be a measure.
You could create a date table with a one-to-many relationship to your fact table and then use a date slicer set to "Between" for your start and end dates. If you wanted to only show those records which share a date with other records then you could try creating a measure like
Num entries on date = CALCULATE( COUNTROWS( 'Table'), ALLEXCEPT( 'Table', 'Table'[Date]) )
then use that as a visual filter to only show when the value is greater than 1
What I am looking at the end is a having the non-common rows(differences) between the two dates. I am joining the subset of the table for the min date selected and the subset of the table for the max date selected with EXCEPT function. Is there a way to calculate that with a measure?
The basic principle would be to define a measure which can act as a flag to say whether a given row should be visible or not, and use that as a visual filter. Something along the lines of
Visible flag =
var table1 = FILTER( 'Table', some conditions )
var table2 = FILTER( 'Table', different conditions )
var currentValue = SELECTEDVALUE('Table'[Unique ID])
return IF( currentValue IN EXCEPT( table1, table2),1, 0)
then add the unique ID column to a table visual and add the measure as a filter to only show when the value is 1
Thank you very much for your feedback. I've managed to get the solution that I needed with your visible flag answer.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |