Match/Look Up

[Lorien]

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

[Anonymous · Accepted solution]

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]

@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]

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]

@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]

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]

@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]

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?