Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everybody,
I'm struggling with the some interaction on my dashboard, probably because of my lack of experience in Power BI DAX & Modeling.
I have simplified my data model to keep only interesting tables in this case. There is a Fact Table with all the values, and many dimensions tables linked to it (like a STAR schema). In this case I only keep the Univers_dimension, Date_dimensions and Client_dimensions. I also have an external excel file which is containing CRM data.
Univers_dimension and Fact_table are linked through the dimension univers_id
Date_dimensions is linked to the Fact_table through the dimension date_dt
Client_dimension is linked to the Fact_table through the dimension id_client
Client_dimension has an inactive linked with the Date_dimension
id_client values in the Client_dimension are unique.
id_marketing in the CRM is not unique : one client can bet multiple times.
What I would like to do: I would like to be able to slice my CRM data (stakes metric for example) by all the dimensions that I have in my star schema.
Example - if my date slicer (from date_dimension) is between the 13th and 14th of December :
From the CRM data, I know that the client "1111" bets 10 dollars the 14th of December.
The client "2333" bets 1$ the 13th and 5$ the 14th of December => 6$ over the time range.
From the links between Client_dimension>Fact_table>Univers_dimension, I know that the client "1111" has an account open in SPORT while the client "2333" is associated to POKER.
So if I filter by the dimension univers_opening I should be able to see the split of the stakes (between POKER and SPORT). But it's not working at all. First, I though it was because the date_dt (Fact_table) was not in the time range selected (date_dt from Date_dimension). But even if the date slicer starts the 12th of December, nothing is happening.
Here is my formula :
CRM stakes =
CALCULATE(
SUM(crm[stakes]),
USERELATIONSHIP(crm[activity_date],date_dimension[date_dt])
)
I attached a sample of my PBI : https://we.tl/t-xUOWuYz8NJ
Thank you in advance for your help!
EDIT : in some cases, it is possible to find the same id_client in the Fact_table. For those cases, only the one with the most recent date should be considered.
Solved! Go to Solution.
Hi @Anonymous ,
I updated your sample pbix file(see attachment), please check whether it can get your expected result.
CRM stakes =
VAR _allseluniv =
ALLSELECTED ( 'univers_dimension'[univers_opening] )
VAR _selclient =
CALCULATETABLE (
VALUES ( 'fact_table'[id_client] ),
FILTER (
'univers_dimension',
'univers_dimension'[univers_opening] IN _allseluniv
)
)
VAR _selmid =
CALCULATETABLE (
VALUES ( 'crm'[id_marketing] ),
FILTER ( 'client_dimension', 'client_dimension'[id_client] IN _selclient )
)
RETURN
CALCULATE (
SUM ( crm[stakes] ),
FILTER ( 'crm', 'crm'[id_marketing] IN _selmid ),
USERELATIONSHIP ( crm[activity_date], date_dimension[date_dt] )
)
Best Regards
Hi @Anonymous ,
I updated your sample pbix file(see attachment), please check whether it can get your expected result.
CRM stakes =
VAR _allseluniv =
ALLSELECTED ( 'univers_dimension'[univers_opening] )
VAR _selclient =
CALCULATETABLE (
VALUES ( 'fact_table'[id_client] ),
FILTER (
'univers_dimension',
'univers_dimension'[univers_opening] IN _allseluniv
)
)
VAR _selmid =
CALCULATETABLE (
VALUES ( 'crm'[id_marketing] ),
FILTER ( 'client_dimension', 'client_dimension'[id_client] IN _selclient )
)
RETURN
CALCULATE (
SUM ( crm[stakes] ),
FILTER ( 'crm', 'crm'[id_marketing] IN _selmid ),
USERELATIONSHIP ( crm[activity_date], date_dimension[date_dt] )
)
Best Regards
I'm still working on it but I haven't found a solution yet. Is my problem not well explained? Let me know if I can help you to understand it!
I'm still working on this issue. I modified my formula by the following one :
CRM stakes =
CALCULATE(
SUM(crm[stakes]),
USERELATIONSHIP(crm[activity_date],date_dimension[date_dt]),
USERELATIONSHIP(crm[id_marketing], fact_table[id_client]),
CROSSFILTER(date_dimension[date_dt],fact_table[date_dt], None)
)
It is "almost" working, but only the first id_client record is associated to the dimension univers_opening (cf picture below). The other problem with this measure, it's that it doesn't handle the case where the same id_client is present in the Fact_table.
I though about applying this DAX measure on a calculated table groupby the id_client but it didn't work neither.
Any help would be more than appreciated,
Thank you 🙂
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |