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'm trying to create a filter so I can filter products that are/aren't classified as "Killers" in my company marketing strategy. My data model is structured this way:
The 'Killers' table is connected via DirectQuery to the 'Produto' table (product info lookup table) by the product's SKU identifier in a one-to-many relationship. The problem is that not every product is considered a "killer" and when I put the Killers['Killer'] column (a "yes" classifier for every single product in the 'Killers' table) as a slicer into my report I can only choose to filter the page as only the products of the said table (just the ones considered "killers") or all the products in general, I can only see a "yes" filter option, that is I can't look at the performance of the products that are "not killers" separately...
Is that a way to create a measure or calculated column inside PBI through DAX so I can use it as a slicer in my dashboards, separating the data into "killers" and "not killers"?
Thanks!
Solved! Go to Solution.
Hi @Anonymous ,
You can use Lookupvalue() function to create the corresponding killer columnn based on the two related columns:
Killer = LOOKUPVALUE(Killers[Killer],Killers[SKU],'PRODUTO'[CODIGO PRODUTO])
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , You can have a calculated column in the direct query, a simple one. Try this
a new column
if([Killer] = "Yes", "Killer", "Non Killer")
see if you can use that
Thanks for the reply, @amitchandak!
Unfortunately, this is not possible... Creating this new column in the 'Killers' table leaves me with the same problem and I can't create the column in the 'PRODUTO' table, the reference to 'Killers'[Killer] does not appear when writing the formula and apparently I can't use RELATED() because of the 1-* relationship...
Hi @Anonymous ,
You can use Lookupvalue() function to create the corresponding killer columnn based on the two related columns:
Killer = LOOKUPVALUE(Killers[Killer],Killers[SKU],'PRODUTO'[CODIGO PRODUTO])
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @v-yingjl. Thanks for the response!
The problem that I am facing is that not every product that is in 'PRODUTO[CODIGO PRODUTO]' is in the 'Killer[SKU]', "Killers" is a subgroup of the total product list...
'PRODUTO' is my product lookup entry so in theory there are no duplicate SKUs in it, and 'Killers' is also, though much smaller, a lookup table, a subset of all my SKUs that are considered "killers".
Based on your example is like the 'Killers[SKU]' didn't have any value for the product B or E, these products are not killers because they don't appear in the 'Killers' table, only in the 'PRODUTO' table.
Hi @Anonymous ,
LOOKUPVALUE() is used to find the corresponding values in tables. If B and E are not in 'Killers' table, the result would not be changed.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |