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 someone help me, please?

 




 

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

8 Replies

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

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        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.