Forum Discussion

jc508's avatar
jc508
Frequent Visitor
10 years ago

Restricting dimensions to used values

Hi,
I am using Power BI over an SSAS Tabular model.
We have several generic dimensions, like Nationality, which usually come from standardised data sets and have many entries.
Any particular fact table only uses a few of these.
For example there are 120 Nationalities listed but our staff come from only 20 of these.
How can I restrict the list of dimensions available in slicers to just the 20 that are used ?
This could be in either the SSAS or the Power-BI areas.


Any ideas appreciated

JC

 

Tips and Tricks

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    10 years ago

    Hi jc508,

     

    Actually, you can create a dataset storing nationalities in PowerBI and create relationship between your data. Then use this as Slicer. Try it and let me know if it helps.

    • jc508's avatar
      jc508
      Frequent Visitor
      9 years ago

      Sam,

      thanks for the thought and sorry for the delayed response (I actually got some holidays!)

       

      I think your idea kind of hurts the idea of shared / conformed dimensions especially when they come from external sources.

       

      JC

  • jc508's avatar
    jc508
    Frequent Visitor
    9 years ago

    Here is a solution I found.
    To SSAS The tables setup in Visual Studio are actually views of objects dimensions in the data database
    so I changed the views to include an addition to the where clause
         AND dds_code_sk in (select distinct Nationality_sk from [Data].FACT_FT
    ie where the key is actually in use on the Fact.
    Now only used values appear in the dimensions of the cube

    Its not efficient I am sure but it works!

     

    JC