How to get Dimension Column based on the selected time period in DAX

[likhithar]

Hello All,

I have a Dimension table where Dimension ID will be dynamic(may in one month few dimension id may added and in another monther month few dimension id may be removed) and it will maintained with YYYYMM Column. 

From the above Image, If I relate Dim Table and Fact Table , the relationship will be Many-Many, to avoid that I created Bridge Table with DAX 

Bridge Table = DISTINCT(DimTable[Town ID])

The other way I tried for Bridge Table is

Bridge Table = CALCULATETABLE(DISTINCT(DimTable[Town ID]),FILTER(DIMTABLE,DIMTABLE[YYYYMM]=MAX(DIMTABLE[YYYYMM])

With the abovre approach , I'm getting MAX month in DimTable Dimension values only for every month in Bridge Table

And the I did Modeling as below

Now my requirement is if One Town ID is removed in Dim Table for a month (Ex:Town ID 2 for 202109 in Dim Table is removed  , Bridge it should get updated(there should not be town ID 2 in Bridge Table for 202109) .If I select (202108, town ID 2 should be there in Bridge Table as it was there in Dim Table).

How to achieve this scenario, I need the Help??

Or suggest any other approach to achieve this??TIA

@amitchandak @Greg_Deckler 

[v-rzhou-msft · Accepted solution]

Hi @likhithar 

We couldn't create a dynamic caclualted table filtered by slicer or filter like a measure. Here I suggest you to solve your problem by change relationship direction or create a filter measure.

1. Change relationship direction

Result is as below.

YYYYMM = 202109

YYYYMM = 202108

2. Create a filter measure

Measure = 
VAR _Bridge = VALUES(DimTable[Town ID])
RETURN
IF(MAX('Bridge Table'[Town ID]) IN _Bridge,1,0)

 YYYYMM = 202109

YYYYMM = 202108

Best Regards,
Rico Zhou

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