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.
ID | Amount | Receivable |
134 | 392.66 | 0 |
134 | 392.66 | 392.66 |
134 | 8236.88 | 8236.88 |
152 | 2492.09 | 0 |
152 | 2532.16 | 2532.16 |
152 | 12628.74 | 0 |
152 | 12628.74 | 12628.74 |
I am having issues eliminating the rows in Red. I would love to get rid of rows where Amount is the same per ID and Receivable is 0 (like the rows in red). Any help please?
Solved! Go to Solution.
Then add calculated column to table.
Something like...
Flag = IF ( OR ( Table1[Receivable] <> 0, CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, [ID] = EARLIER ( Table1[ID] ) && [Amount] = EARLIER ( Table1[Amount] ) ) ) = 1 ), 1, 0 )
Then... you can use [Flag]<>0 in your measures as part of filter context... or create new table using.
NewTable = FILTER((Table1,Table1[Flag]<>0)
If using DAX, you could create new table using following.
Table = SUMMARIZE ( Table1, Table1[ID], Table1[Amount], "Recievable", MAX ( Table1[Receivable] ) )
If in Query Editor using "M"... you can transform the table itself by...
Select "ID" & "Amount" columns. Right click -> Group by. Name Aggregate column "Recievable" and Max of "Receivable column.
= Table.Group(#"Changed Type", {"ID", "Amount"}, {{"Recievable", each List.Max([Receivable]), type number}})
Thanks alot. But with this approach, this will only work if Receivable is 0 in all cases of duplicate Amount per ID. If at any point, there is a record like the last record here (it's a newly added row for the purpose of explaining what i mean):
ID | Amount | Receivable |
134 | 392.66 | 0 |
134 | 392.66 | 392.66 |
134 | 8236.88 | 8236.88 |
152 | 2492.09 | 0 |
152 | 2532.16 | 2532.16 |
152 | 12628.74 | 0 |
152 | 12628.74 | 12628.74 |
152 | 12628.74 | 9628.74 |
It will also be kicked out cos of the MAX aggregation. Meanwhile, I want records like that to remain
Then add calculated column to table.
Something like...
Flag = IF ( OR ( Table1[Receivable] <> 0, CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, [ID] = EARLIER ( Table1[ID] ) && [Amount] = EARLIER ( Table1[Amount] ) ) ) = 1 ), 1, 0 )
Then... you can use [Flag]<>0 in your measures as part of filter context... or create new table using.
NewTable = FILTER((Table1,Table1[Flag]<>0)
PERFECT! This worked. Thanks!
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |