Createing a slicer for missing values in a certain table

[Anonymous]

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!

[amitchandak]

@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

[Anonymous]

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...

[v-yingjl · Accepted 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]

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.

[v-yingjl]

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.