cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Removing duplicates, keeping rows where a field holds a certain value

Got a computed table of 3 columns. There are more than a million rows and a few thousand of major keys in the table.

To simplify, the eight top rows only are shown below.

Challange: For major key = K-1 there are two of Colour = Green.
I want to remove one of the rows of the Green colour within major key K-1, ensuring that I keep the row with a X in the Flag column.
Is it possible using DAX?
I am new to DAX, so please excuse if this was too trival.

Thank you!

Current table:

 Major key Flag Colour K-1 X Red K-1 Blue K-1 X Green K-1 Green K-1 X White K-1 X Yellow K-2 Black K-2 X White

Wanted result, Major key + Colour have become unique:

 Major key Flag Colour K-1 X Red K-1 Blue K-1 X Green K-1 X White K-1 X Yellow K-2 Black K-2 X White
2 ACCEPTED SOLUTIONS
Community Support

Hi @rjoha ，

``````Table 2 =
VAR tab =
'Table',
"column",
COUNTROWS (
FILTER (
'Table',
'Table'[Major key] = EARLIER ( [Major key] )
&& 'Table'[Colour] = EARLIER ( [Colour] )
&& 'Table'[Flag] > EARLIER ( [Flag] )
)
)
)
RETURN
CALCULATETABLE (
DISTINCT ( 'Table' ),
FILTER (
tab,
'Table'[Flag] <> BLANK ()
|| (
'Table'[Flag] = BLANK ()
&& [column] = BLANK ()
)
)
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Eventually, got this simple SUMMARIZE to work:

`tab2 = SUMMARIZE(tab1,tab1[Major key],tab1[Colour],"Flag", CALCULATE(MAX(tab1[Flag])))`
4 REPLIES 4
Frequent Visitor

How would I get the table to show only the first Major Key, like this:

 Major key Flag Colour K-1 X Red Blue X Green X White X Yellow K-2 Black X White

Thanks,

Tomas

Community Support

Hi @rjoha ，

``````Table 2 =
VAR tab =
'Table',
"column",
COUNTROWS (
FILTER (
'Table',
'Table'[Major key] = EARLIER ( [Major key] )
&& 'Table'[Colour] = EARLIER ( [Colour] )
&& 'Table'[Flag] > EARLIER ( [Flag] )
)
)
)
RETURN
CALCULATETABLE (
DISTINCT ( 'Table' ),
FILTER (
tab,
'Table'[Flag] <> BLANK ()
|| (
'Table'[Flag] = BLANK ()
&& [column] = BLANK ()
)
)
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Eventually, got this simple SUMMARIZE to work:

`tab2 = SUMMARIZE(tab1,tab1[Major key],tab1[Colour],"Flag", CALCULATE(MAX(tab1[Flag])))`
Continued Contributor

This is easily possible in the Query Editor

Sort the data using KeyFlag so all the "X" are on the top.

Now the code automatically generated will be something like

Table.Sort(#'STEPNAME',{{keyFlag,Order.Descending}}) <-- I am unsure if this will be descending or acending but just get "X to be on top

to be =Table.Buffer (Table.Sort(#'STEPNAME',{{keyFlag,Order.Descending}}))

now remove duplicates by selecting the "Major" and "Color" column

This should get you the result.

Regards,

Moiz

If this post helps, please "Accept" it as Solution to help other members find it.

Thanks,

Moiz
Was I able to answer your question? Mark my post as a solution to help others. Kudos if you liked the solution.