Skip to main content
cancel
Showing results for 
Search instead 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

Reply
rjoha
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 keyFlagColour
K-1XRed
K-1 Blue
K-1XGreen
K-1 Green
K-1XWhite
K-1XYellow
K-2 Black
K-2XWhite

 

 

Wanted result, Major key + Colour have become unique:

Major keyFlagColour
K-1XRed
K-1 Blue
K-1XGreen
K-1XWhite
K-1XYellow
K-2 Black
K-2XWhite
2 ACCEPTED SOLUTIONS
v-kkf-msft
Community Support
Community Support

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 ()
                )
        )
    )

 

 image.png

 

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.

View solution in original post

Eventually, got this simple SUMMARIZE to work:

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

View solution in original post

4 REPLIES 4
Tomas_Seig
Frequent Visitor

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

Major keyFlagColour
K-1XRed
  Blue
 XGreen
 XWhite
 XYellow
K-2 Black
 XWhite


Thanks,

Tomas

v-kkf-msft
Community Support
Community Support

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 ()
                )
        )
    )

 

 image.png

 

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.

Eventually, got this simple SUMMARIZE to work:

tab2 = SUMMARIZE(
tab1,
tab1[Major key],
tab1[Colour],
"Flag", CALCULATE(MAX(tab1[Flag]))
)
moizsherwani
Continued Contributor
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

 

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.

Thanks,

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors