Hello,
I have an issue where my dimension table is changing over the years. What I tried to do to deal with it, is to add a date to all my tables so that I can filter the right lines in my dimension table.
My goal is to have a dynamical dimension table that is changing depending on the date.
Here is a sample of my data:
I have no "Orange Juice" in 2022 in my ref_Company because it isn't in our scope anymore, but it is still here in my fact table (Price):
Ref_Company(Dimension table):
Price(Fact table):
Ref_Date(Dimension table):
What i expected is that, when I select 2022, when I select 2022, we can not see the Orange_index on my visual. But here is what I have:
It seems that it still manage to get the Orange_Index from 2021 in my Ref_Company. So my ref_Company isn't dynamic.
My goal is to have my Ref_company dynamic depending on the date I select on my slicer.
Do you know how can I deal with this issue ? (Note that I have a lot of facts table like my Price table, so I can not just delete the lines about Orange Juice).
And if you think that my solution about adding a date to my Ref_company isn't a good one, feel free to explain another solution to me.
PBIX file: https://www.dropbox.com/s/t58ujxuo1ppl2bm/Dynamic%20dimension%20table%20depending%20on%20the%20Date....
Thank you very much for your help !
Hi @Bastienlin_ ,
Please firstly make the relationship between Price table and Ref_Date table inactive:
Then create a flag measure:
Flag =
var _t=SUMMARIZE(FILTER(ALL('Ref_Company'),[Date]=SELECTEDVALUE('Ref_Date'[Date])),[Index])
return IF(MAX('Price'[Index]) in _t && MAX('Price'[Date])=SELECTEDVALUE(Ref_Date[Date]) ,1,0)And apply it to visual-filter pane, set as "is 1" as shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
