Forum Discussion

Keks's avatar
Keks
Frequent Visitor
2 years ago
Solved

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'   Can someo...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

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's avatar
Keks
Frequent Visitor
2 years ago

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's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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"