Forum Discussion

Liam01's avatar
Liam01
Icon for Helper I rankHelper I
4 years ago
Solved

Find values based on a condition but return all occurrences where there are multiples of the value

Hello,

 

Help required please and hopefully I explain this correctly.

 

I'd like to be able to find where a value meets a condition but it returns all occurrences of that value including the ones that don't meet the condition.

 

So in the below example, data on the left and I've searched for all orders that appear on sheet A. The results show all those but also the occurrences of those orders that are on sheets B and C.

 

Is there a way to do this as a measure - maybe filter by A then shown as a count with the number of total sheets they appear in?

Thanks

 

Order number Sheet ResultsOrder number Sheet
12345A  12345A
12345B  12345B
23456B  34567A
34567A  34567B
34567B  34567C
34567C  89123A
45678B  89123C
45678B    
56789B    
89123A    
89123C    
Need Help
  • Kaly's avatar
    Kaly
    4 years ago

    Hi Liam01 ,

    According to your description, here's my solution. Create a measure.

    Measure =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order number] = MAX ( 'Table'[Order number] )
                && 'Table'[Sheet] = "A"
        )
    ) > 0,
    1
)

    Put the measure in the visual filter and select its value to 1.

    Get the correct result.

    Best Regards,

    Kaly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

13 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    So for each sheet you want to list the orders that only appear on that sheet? Or do you want to list the number of sheets each order appears in?  What about 45678? Should it count once or twice?

    • Liam01's avatar
      Liam01
      Icon for Helper I rankHelper I
      4 years ago

      Hi,

       

      I want to show the orders that appear in sheet A but also show which other sheets those orders appear in. 45678 wouldn't appear as it's in sheet B only.

      Thanks

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago
        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyVIrVQfCcwDwQxwzOA3HM4SphPFQ5ZzAPxLGAy6HyQBxLOM/CEmgj3EwYD2hKLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, Sheet = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Sheet"}, {{"Rows", each _, type table [#"Order number "=nullable text, Sheet=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Intersect({[Rows][Order number],#"Grouped Rows"{0}[Rows][Order number]})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Sheet", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
    #"Grouped Rows1" = Table.Group(#"Expanded Custom", {"Custom"}, {{"Sheets", each _, type table [Sheet=nullable text, Custom=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom.1", each Text.Combine([Sheets][Sheet],", ")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom", "Custom.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Custom", "Order"}, {"Custom.1", "Sheets"}})
in
    #"Renamed Columns"

        How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

  • Kaly's avatar
    Kaly
    Icon for Resolver II rankResolver II
    4 years ago

    Hi Liam01 ,

    According to your description, here's my solution. Create a measure.

    Measure =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Order number] = MAX ( 'Table'[Order number] )
                && 'Table'[Sheet] = "A"
        )
    ) > 0,
    1
)

    Put the measure in the visual filter and select its value to 1.

    Get the correct result.

    Best Regards,

    Kaly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Liam01's avatar
      Liam01
      Icon for Helper I rankHelper I
      4 years ago

      Hi Kaly

       

      That seems to have done the trick and what I was after, thanks for that.

       

      Thanks

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    let

         Source = <your source query>,

        ...

    • Liam01's avatar
      Liam01
      Icon for Helper I rankHelper I
      4 years ago

      Hi,

       

      I don't understand which part of the text this should replace - is the Source added before Table.FromRows? So

      let

      Source = #"Query Name", Table.FromRows...

       

      I've done this and get a 'Token Equal expected' error?

       

      Or is it this entire section after Source = ?

      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lFyVIrVQfCcwDwQxwzOA3HM4SphPFQ5ZzAPxLGAy6HyQBxLOM/CEmgj3EwYD2hKLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order number" = _t, Sheet = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Sheet"}, {{"Rows", each _, type table [#"Order number "=nullable text, Sheet=nullable text]}}),