Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everybody, How are you?
ı am new at power bi and have question. ı am waiting for your helps.
-----------------------------
question:
dımensıon table | |||
ıd | first_date | last_date | numbers |
1 | 10.1.2021 | 22.1.2021 | 1 |
1 | 22.1.2021 | 23.2.2021 | 5 |
1 | 23.2.2021 | 30.3.2021 | 8 |
2 | 13.1.2021 | 20.2.2021 | 8 |
2 | 20.2.2021 | 30.5.2021 | 10 |
fact table | ||
ıd | date | numbers |
1 | 25.1.2021 | ? |
2 | 30.2.2021 | ? |
ı have two table which has connection each other by ID column. What ı want to do is, bring "numbers" value from dımensıon table which row 'fact_table'.[date] between 'dim_table'.[first_Date] and 'dim_table'.[last_Date].
so target will be like this:
fact table | ||
ıd | date | numbers |
1 | 25.1.2021 | 5 |
2 | 30.2.2021 | 10 |
how to work- >for first row(ıd=1) ->
firstly look at the ıd column and then go to dımensıon table, fınd the ıd=1 rows and then date=25.1.2020 and check to rows which 25.1.2021 is between first date and last date and then fınd to correct row and take to number value which is '5' . ı want to do this .
how to do that?
ı really need help.
Thanks,
Solved! Go to Solution.
Hi, @Trist
Please try to create the mode like below, and write the calculated measure like below.
Please check the sample pbix file, link down below.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
In Table2, write this calculated column formula
=calculate(sum(table1[numbers]),filter(table1,table1[first_date]<=earlier(table2[date])&&table1[last_date]>=earlier(table2[date])))
Hope this helps.
Hi,
In Table2, write this calculated column formula
=calculate(sum(table1[numbers]),filter(table1,table1[first_date]<=earlier(table2[date])&&table1[last_date]>=earlier(table2[date])))
Hope this helps.
Hi, @Trist
Please try to create the mode like below, and write the calculated measure like below.
Please check the sample pbix file, link down below.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
79 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |