Searching Substring in VALUES

[Anonymous]

Hi

I have a Measure that finds all the items in my main table where a certal car model is selected.
Here's the DAX

Leads Qualified Consumer (1) Model = 
        IF(
            SELECTEDVALUE('Purchase Order'[Model])<>"All models";
            countrows(filter(Leads;Leads[Qualification]="Qualified" && Leads[Type - New 1]=TRUE() && Leads[Model Name] = SELECTEDVALUE('Purchase Order'[Model])))
;countrows(filter(Leads;Leads[Qualification]="Qualified" && Leads[Type - New 1]=TRUE())))

So this, if put in a pivot, gets all the rows in the LEADS table that are Qualified and Type 1 and IF not "All models" will also search for specific model.

This works, the next step is that sometimes "models" is more than one, so I created a table connected to my main Purchase Order with all the list of models for any specific row, and the new query is this

Leads Qualified Consumer (1) Model2 = 
        IF(
            SELECTEDVALUE('Purchase Order'[Model])<>"All models";
            countrows(filter(Leads;Leads[Qualification]="Qualified" && Leads[Type - New 1]=TRUE() && Leads[Model Name] in values('Purchase Order - Models'[Model]) ))


;countrows(filter(Leads;Leads[Qualification]="Qualified" && Leads[Type - New 1]=TRUE())))

So the trick here is getting the VALUES of the related table (which is filtered by the filter context) and do a "IN" this list.

Now the next step is that sometimes models are "substring": for example the model that I want to count might be "A Class" and the actual model in the leads table might be written as "A Class Business" and this one should count. 

So what I should do is not a "IN" but a "IN where substring". So I should extract the leftmost part of the models in the Leads table where the number of characters is the length of "A Class", and then do a "IN" this list. Keeping in mind that the list of models to be searched could be made of several cars of different length, for example "A Class" and "GLA"...

any suggestion?

[v-danhe-msft]

Hi @Anonymous,

Could you please share some sample file to have a test and post your desired result if possible?

Regards,

Daniel He

[Anonymous · Accepted solution]

Hi

thanks, I solved using SEARCH

sumX('Purchase Order - Models';countrows(filter(Leads;Leads[Qualification]="Qualified" && Leads[Type - New 1]=TRUE() && searcH('Purchase Order - Models'[Model];Leads[Model Name] ;1;-1)>0 )));

So this cycle through all the "models" and return true if it finds all values where the text is found