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 , 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!

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Lorien , Try like

     

    Laverde Nova Check =
    var _cnt = Countx(filter(allselected(q3QuoteFeatureTreatment), [FabricSupplier] =1394 && [FabricName] ="Nova" && [QuoteCode] = max([QuoteCode])),[QuoteCode])
    return
    if(isblank(_cnt) , "No", "Yes")

    • Lorien's avatar
      Lorien
      Frequent Visitor
      5 years ago

      That fixed the measure so I don't get an error but I only get a No result whereas I know there is more than one quotecode with both options in it as per my original screen shot above.

       

      I have filtered the below list by just drape and voile-sheer treatmentdescription but there are zero yes results even without the filter on:

       

       

      So something is not quite right somewhere as we know from the my first post that there are quotecodes with both options in them. Any other ideas please?