Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |