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 '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!

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.

5 Replies

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