Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Createing a slicer for missing values in a certain table

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 'Killer...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      v-yingjl
      Community Support
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.