Forum Discussion

Lorien's avatar
Lorien
Frequent Visitor
5 years ago
Solved

Match/Look Up

Hi everyone

 

Can anyone please help me on the below? I am interested returning a positive result if a quote code contains Laverde Textile fabric as shown on the list below to the left AND that it also contains the Nova fabric as well. All rows come from the same table.

 

 

Any help would be very much appreciated.

 

Thanks

Lorien

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Lorien 

    I extracted part of the data you provided and created a simple sample .

    Then create a measure like this :

    Laverde-Nova check =
var _number=CALCULATE(COUNT('Table'[QuoteCode]),FILTER('Table','Table'[SupplierName]="Laverde Textile" && 'Table'[FabricName]="Nova"))
return IF(_number=0,"No","Yes")

    First calculate the number of rows that meet the conditions, and then determine whether the number of rows is 0. If it is, it means that there are no values that meet the conditions. If it is not, then there are rows in the table that contain both "Laverde Textile" and "Nova".

    I have attached my pbix file ,you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

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

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Lorien , Create a new column like

     

     

    new column =
    var _cnt = Countx(filter(Table, [supplierName] ="Laverde Textile" && [FabricName] ="Nova" && [Quote] = ealier([Quote])),[Quote])
    return
    if(isblank(_cnt) , "No", "Yes")

    • Lorien's avatar
      Lorien
      Frequent Visitor
      5 years ago

      Unfortunately the rows do not contain the same data so I may find one but not the other, I need it to check the rest of the table. I don't think this can be a calculated column for that reason but a measure? Can the above be adapted for a measure as I think that will work.

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Lorien , I think earlier should do that job in case of a new column

         

        Measure can be

         

        new measure =
        var _cnt = Countx(filter(allselected(Table), [supplierName] ="Laverde Textile" && [FabricName] ="Nova" && [Quote] = max([Quote])),[Quote])
        return
        if(isblank(_cnt) , "No", "Yes")

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Lorien 

    I extracted part of the data you provided and created a simple sample .

    Then create a measure like this :

    Laverde-Nova check =
var _number=CALCULATE(COUNT('Table'[QuoteCode]),FILTER('Table','Table'[SupplierName]="Laverde Textile" && 'Table'[FabricName]="Nova"))
return IF(_number=0,"No","Yes")

    First calculate the number of rows that meet the conditions, and then determine whether the number of rows is 0. If it is, it means that there are no values that meet the conditions. If it is not, then there are rows in the table that contain both "Laverde Textile" and "Nova".

    I have attached my pbix file ,you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

     

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