Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Keks
Frequent Visitor

Delete rows only if

Hi everybody, 

 

Would like to find a way to flag rows when :

- sequence is unique in the table 
or

- if sequence is not unique, then column description must be "Changed Qty'

Keks_2-1706887894285.png

 

Can someone help me, please?

 




 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Keks Try:

Measure = 
    VAR __Seq = MAX('Table'[Sequence])
    VAR __Result = 
        SWITCH(TRUE(),
            MAX('Table'[Description]) = "Changed Qty", 1,
            COUNTROWS(FILTER(ALL('Table'), [Sequence] = __Seq)) = 1, 1,
            0
        )
RETURN
    __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@Keks Try:

Measure = 
    VAR __Seq = MAX('Table'[Sequence])
    VAR __Result = 
        SWITCH(TRUE(),
            MAX('Table'[Description]) = "Changed Qty", 1,
            COUNTROWS(FILTER(ALL('Table'), [Sequence] = __Seq)) = 1, 1,
            0
        )
RETURN
    __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

I haven't tested it yet but could you please explain me? 🙂 

@Keks OK, well, the measure returns 1 for the rows you want and 0 for the rows you don't want. Then you just use the Filters pane to filter for a value of 1. This removes the 0's from your visual. It's a version of a Complex Selector. The Complex Selector - Microsoft Fabric Community

 

If you want to actually delete, delete the rows you would need to do that in Power Query but PQ doesn't have a switch statement so you would have to use nested if then else statements. PBIX is attached below signature.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

It is working perfecly well... So thank you. 
I just need to understand all the functions used :-).

One more time, thank you!

Keks
Frequent Visitor

@Greg_Deckler not possible to use this measure in a slicer or a filter. How can I do ? 😞 

@Keks Updated PBIX. You can use a Measure in the Filters pane for visual level filters only (not page or report level filters). OK, so MAX just gets teh maximum value in context. You have to use an aggregation function when referring to columns. The SWITCH(TRUE() ) statement is a fancy way of doing nested IF statements much more cleanly. So when using SWITCH(TRUE() ), the rest of the parameters come in pairs. The first part of the pair is something that returns a logical true or false and the second part of the pair is what to return if the statement is true. So, the first pair just checks to see if the Description is Changed Qty and if so, returns 1. The second counts all the rows in the table where the Sequence is the current sequence in context (__Seq variable) and if that count is equal to 1 it returns 1. If neither of these conditions is met, it returns 0.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

Finally, I would like to delete rows (they are useless) in the data instead of flagguing it by a measure.

How can I do that ? 

@Keks For that you would need to use Power Query. Updated PBIX is attached.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQFQiUdJb/8kozMvHQgyzGoxBBIGRooxeog5J0zEvPSU1MUAksqQWqKwGpMoEqc0I0oKjFCNsJJ14kIeQwrjCBWxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sequence = _t, Description = _t, Article = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sequence", type text}, {"Description", type text}, {"Qty", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Sequence"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Columns", each _, type table [Sequence=nullable text, Description=nullable text, Article=nullable text, Qty=nullable number]}}),
    #"Expanded Columns" = Table.ExpandTableColumn(#"Grouped Rows", "Columns", {"Description", "Article", "Qty"}, {"Description", "Article", "Qty"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Columns", "Keep", each if [Count] = 1 then 1 else if [Description] = "Changed Qty" then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Keep"})
in
    #"Removed Columns"

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.