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,
I have the following data model :
CRM data
id_client | activity_date | stakes |
2233 | 12/12/2021 | 3,00 |
2333 | 13/12/2021 | 1,00 |
2333 | 14/12/2021 | 5,00 |
1111 | 14/12/2021 | 10,00 |
Fact table
id | date_dt | universe_id | id_client | leads | metric_2 |
1 | 11/11/2021 | 1 | 1111 | 0 | 342 |
2 | 12/12/2021 | 137 | 2233 | 1 | 0 |
3 | 12/12/2021 | 138 | 1111 | 1 | 0 |
4 | 12/12/2021 | 1 | Not available | 0 | 2 |
5 | 12/12/2021 | 1 | Not available | 0 | 21 |
Universe_dimension
univers_id | source_id | univers_opening |
137 | 6 | POKER |
138 | 6 | SPORT |
1 | 6 | not available |
I would like to know the sum of Stakes by the univers_opening between the 11 and 14th of December. To do that, I first need to know to which univers_opening each id_client (from the CRM) is associated. As you can see, id_client (fact table) can be duplicated. In this case, I would like to keep the row with the most recent date.
Expected result will look like this :
univers_opening | CRM stakes |
POKER | 9 |
SPORT | 10 |
not available | 0 |
I tried with the following formula :
CRM stakes =
CALCULATE(
SUM(crm[stakes]),
USERELATIONSHIP(crm[id_client], fact_table[id_client])
)
but it's giving me a wrong result:
univers_opening | CRM stakes |
POKER | 3 |
SPORT | 10 |
not available | 10 |
Total | 19 |
I can provide the pbix file if needed.
Thank you for your help
Solved! Go to Solution.
Hi @Anonymous ,
For your description, what I want to confirm with you is whether 2233 and 2333 are different id types or input errors. Also for your data model, I think you need to create a bridge table between the CRM and Fact tables. Then try again.
If the problem is still not resolved, please point it out. Looking forward to your feedback.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
For your description, what I want to confirm with you is whether 2233 and 2333 are different id types or input errors. Also for your data model, I think you need to create a bridge table between the CRM and Fact tables. Then try again.
If the problem is still not resolved, please point it out. Looking forward to your feedback.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-henryk-mstf
2233 and 2333 were input erros. With the same value (2233), my formula is working well... It is my bad.
Thank you about mentioning the bridge tables, I did not know about it. It will be useful for later DAX measures. Thank you !
Hi @Anonymous ,
Thank you very much for your feedback.
If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly.
Best Regards,
Henry
Hi guys,
Does anyone has an idea about how to solve this issue?
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |