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'm looking for some help with identifying rows as duplicates ONLY if they are duplicated in the current filter context.
I'm having a really hard time doing this.
I can easily mark a duplicate row by creating a custom column by itterating through the table then adding a Yes/No or 1/0 but that only works in the context of the whole dataset when using a custom column.
E.g. whole dataset might look like
Date | ID |
2020-01-01 | A |
2020-01-01 | B |
2020-01-02 | C |
2020-01-03 | A |
In my report visual I just pin a simple table containing both columns and a Date slicer against the date.
If my slicer date range is the entire table then I need ID=A marked as duplicate.
If my slicer date range is only for the first 2 dates (2020-01-01 -> 2020-01-02) then there are no duplicates.
Any help would be great!
Solved! Go to Solution.
Try using ADDCOLUMNS to COUNTROWS against a SUMMARIZE
Try using ADDCOLUMNS to COUNTROWS against a SUMMARIZE
Thanks for pointing me in the right direction.
I ended up using that to create a new table with ID and number of duplicates. Initially it was just showing the whole table regardless of my date slicer then I reasised the slicer was using the date from the other table.... so I created a master calendar table and use that as the date slicer instead so it now affects both my DAX calculated table and the original.
Basically I created a table using this:
Table =
ADDCOLUMNS(
SUMMARIZE(Sheet1,Sheet1[ID], Sheet1[Date]),
"Duplicate",calculate(COUNTROWS(Sheet1))
)
Then a master calendar table using CALENDARAUTO() and creating a relationship between the date in the master calendar and Sheet1 & Table.
I also created another bridging table for [ID] so I can now also drill through from my newly created table using [ID] to 'Sheet1'.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |