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 All,
I have one table with duplicated rows. I would like to remove these duplicates. It can be achieved by using one column and keeping the highest index. Here is a simple example. For each period and each item, I would like to have only 1 row.
I have tried this formula:
_MyTable2 =
var _maxindex = CALCULATE ( MAXX (Table_test, Table_test[category_index] ) , ALLEXCEPT ( Table_test, Table_test[item] ) )
RETURN
FILTER (Table_test, Table_test[category_index] = _maxindex )
In January, I have 2 rows for item A and I would like to keep only the one with the max index (=2)
In January, I have 1 row for item B and I want to keep it. My formula works.
However, in February I should have 1 row for each item with same logic. In that case, the row with the item A is not retreived, just like if the index of A (1) is compared with the index of B (2). Please see the 2 tables below (initial and expected). I hope you can help me solve this. Thanks in advance.
The initial table :
Period | item | category | category_index |
01/01/2021 | A | X | 2 |
01/01/2021 | A | Y | 1 |
01/01/2021 | B | X | 2 |
01/02/2021 | A | Y | 1 |
01/02/2021 | B | X | 2 |
01/02/2021 | B | Y | 1 |
01/03/2021 | A | X | 2 |
01/03/2021 | A | Y | 1 |
And the expected output:
Period | item | category |
01/01/2021 | A | X |
01/01/2021 | B | X |
01/02/2021 | A | Y |
01/02/2021 | B | X |
01/03/2021 | A | X |
Solved! Go to Solution.
You can create a flag column new column
=
var _max - maxx(filter(Table, [Period] = earlier([Period]) && [item] = earlier([item] ) ) , [category_index] )
return
if([category_index] =_max, false(), true())
@Arnault_ Please try:
You can create a flag column new column
=
var _max - maxx(filter(Table, [Period] = earlier([Period]) && [item] = earlier([item] ) ) , [category_index] )
return
if([category_index] =_max, false(), true())
Thanks for your ansnwer.
I actually created 2 columns applying the same logic:
Max_index = CALCULATE ( MAX ( Table_test[category_index] ),
ALLEXCEPT ( Table_test, Table_test[item],Table_test[Period] )
)
Max_Index_YorN =
If ( Table_test[Max_index] = Table_test[category_index], 1, 0 )
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |