Delete rows only if

[Keks]

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'

Can someone help me, please?

[Greg_Deckler · Accepted solution]

@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

[Keks]

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

[Greg_Deckler]

@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.

[Keks]

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

One more time, thank you!

[Keks]

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

[Greg_Deckler]

@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.

[Keks]

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 ? 

[Greg_Deckler]

@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"