Forum Discussion
Helper IIDynamical dimension table depending on the date selected
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):
What i expected is that, 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.
Do you have a solution to have a dynamical Ref_Company, depending on the date for example or maybe another idea ? (Note that I have a lot of facts table like my Price table, so I can not just delete the lines about Orange Juice)
Pbix link:
https://www.dropbox.com/s/t58ujxuo1ppl2bm/Dynamic%20dimension%20table%20depending%20on%20the%20Date....
Thank you very much for your help !
4 Replies
- amitchandak
Super User
Bastienlin_ , one of the way to deal with this you have concatenated key in both tables and you join on those
Key = [Index] & "-" & [year]
or
Key = [Index] & "-" & year([Date])
Other wise you get the columns which are not joined
measure = //assume joined on index
var _tab = summarize(allselected(Table1), Tabel1[Year])
return
calculate(sum(Table2[price]) , filter(Table2, Tabl2[Year] in _tab) )
- Ashish_Mathur
Bastienlin_Hello Ashish_Mathur ! Thank you for your answer, but could you please explain me how can I open it on PBI ? Because what I see when I extracted your file is this:
- Ashish_Mathur
Hi,
Double clicking the file should work. If not, then you are probably using an older version of PBI Desktop.
