Slicer-show only those values from dimension table based on whether it is present in the fact table

[Anonymous]

Hi All,

I am very new to power BI. I searched the whole power BI community.

This is quite urgent.

I have a dimension table 'DIM_DATE' with D_KY, YR, MTH columns. I have a fact table 'Sales' with multiple columns D1_KY, D2_KY... which are FKs (Many to one relation wih D_KY of DIM_DATE). Now I need a slicer with values from YR column that are present in Fact table only but not all the YR values from the DIM_DATE. I have already tried cross filter(both)option, it isn't working and I shouldn't use 'Merge' to do this.

I need the slicers as below.

Slicer 1 - Show YR based on DIM_DATE.D_KY=Sales.D1_KY

Slicer 2 - Show YR based on DIM_DATE.D_KY=Sales.D2_KY

Appreciate all the help.

Thanks,

S

[amitchandak]

@Anonymous , Join both dates with date table one join will be inactive. you can activate that in measure using userelationship

refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970