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,
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 🙂
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 |
---|---|
44 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
70 | |
49 | |
45 | |
20 | |
16 |