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

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

      • Lorien's avatar
        Lorien
        Frequent Visitor
        5 years ago

        Thank you, we are almost there!

         

        I changed the above measure to the below:

        Laverde Nova Check =
        var _cnt = Countx(filter(allselected(q3QuoteFeatureTreatment), [FabricSupplier] ="1394" && [FabricName] ="Nova" && [QuoteCode] = max([QuoteCode])),[QuoteCode])
        return
        if(isblank(_cnt) , "No", "Yes")
         
        The reason for the "1394" is because it is the code for that supplier. The list of names of the suppliers comes from a seperate table. I have them linked together in the relationships by SupplierCode

         

        However I think the use of that column broke the measure because of the below error:

         

         

        Should I merge the two tables together to resolve that or is there another way?

         

        Thanks again in advance!