Forum Discussion
Searching Substring in VALUES
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?
- Anonymous7 years ago
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
2 Replies
- v-danhe-msftMicrosoft Employee
Hi Anonymous,
Could you please share some sample file to have a test and post your desired result if possible?
Regards,
Daniel He
- AnonymousNot applicable
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