Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Hi @Anonymous,
Could you please share some sample file to have a test and post your desired result if possible?
Regards,
Daniel He
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |