Interactions between shared dimensions & fact tables

[Anonymous]

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

[v-henryk-mstf · Accepted 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.

[Anonymous]

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 !

[v-henryk-mstf]

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

[Anonymous]

Hi guys,

Does anyone has an idea about how to solve this issue?