Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Slicers Management with modeled data - PLEASE HELP!!!

I have three datasets that are joined to show a table in Power BI.  For example I have Data Set "A" joining Dataset "B" by Order Number, and Dataset "B" joining Dataset "C" by Customer ID.  Dataset "B" is used as the connective tissue between "A" and "C".  

 

The output in the table that shows a comprehensive table whose rows include all of the pertient data my users will need to see.  Yay!

 

Here's my question.  Because the slicers are created from one of the three datasets I am using, they often include records in the slicer not relevant to the modeled data.  i.e. The slicer includes Customer Name from Dataset "A", but there is not data to be joined with this Customer Name from Datasets "B" or "C".

 

How can I condition the slicer to only refect values that are positively modeled?  The below screenshot is a result of selecting a Customer Name (from Dataset "A") in the slicer, that did not have matching records in datasets "B" and "C".  The output below is correct, but it's the slicer that I want to be a better user experience.  Something with logic like, "In the slicer, show me only Customer's Name (Dataset "A") when greater than X orders (Dataset "C").

 

Need Help
Show and Tell

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    If you join the three tables as one table, I would suggest you create the slicer from the "one table". 

    If you join the three tables using relationships, I would suggest you create a new dimensional table from dataset "B" or "C". 

    Another possible solution could be adding a calculated column in dataset "A". If [Customer Name] doesn't exist in "B" or "C", we can leave it blank. Then use the new column in a slicer.

     

    Calculated column =
IF (
    [Customer Name] IN VALUES ( 'B'[Customer Name] ),
    [Customer Name],
    BLANK ()
)

     

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      Can you offer some detail about how to join the table as a single table, and create a dimension table?

      If Inchoose to ise a calculated column can it work if a variable is not shared between all 3 tables?
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

        Can you share a dummy sample file?

        You can join the tables in the Query Editor. But all this depends on your model. Joining tables couldn't be a solution for all situations. 

        Yes, it will work. I assume the 3 tables are connected by relationships. There should be relationships.

         

         

        Best Regards,
        Dale