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

[rjoha]

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

[v-kkf-msft · Accepted solution]

Hi @rjoha ，

Please create a new table:

Table 2 =
VAR tab =
    ADDCOLUMNS (
        '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.

[Tomas_Seig]

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

[v-kkf-msft · Accepted solution]

Hi @rjoha ，

Please create a new table:

Table 2 =
VAR tab =
    ADDCOLUMNS (
        '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.

[rjoha · Accepted solution]

Eventually, got this simple SUMMARIZE to work:

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

[moizsherwani]

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

Modify the code add Table.Buffer

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.