How can visuals, slicers and filters only display Dim values where key exist in FACT

[Anonymous]

Hi Community –

I’m sourcing salesforce objects, including a User object (aka dimension table) with a system generated primary key / id, username, …

Some of those users are salespersons and their ids are foreign keys in FACTs (like Opportunities).

If relational, I could write custom SQL to source only salespersons (performance permitting).  But since my source is cloud, can I use Power Query M or DAX so that visuals, slicers & filters only display the ~2 dozen salespersons’ names and not all 100+ names of SFDC users?

Thanks, Clay

[v-alq-msft]

Hi, @Anonymous 

Based on description, you may create a calculated table which use Distinct(Dim values) and then put the column into the slicer. However, It will cause the issue when the range of Slicer is less than the range of fact table. Here is an example, the slicer can't filter the date range larger than its range. It is suggested that you use the larger range in the slicer.

If I misunderstand your thought, please show me your smaple data and expected result. Do mask sensitive data before uploading, I am glad to solve the problem for you.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

So, the behavior for visuals changes  by toggling "Show items with no data" which I do need for the 1st visual I built but not a subsequent visual I copied.  Still have the issue of slicers and filters displaying all Dim values not just those that exist in FACT.  thanks Clay

[amitchandak]

If its an import (Not direct query). Bidirectional join can help.  In case of direct query bidirectional join can be costly.

[Anonymous]

Thanks Amit.  Sourcing is import.  Relationship is already Cross filter direction = Both.  Changing the direction &/or "Applying Security" appears not to have any impact on the dim values in slicers, filters or visuals.  The relationship with Cross filter direction = Both still behaves like an outer join.