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 everyone,
Firstly please excuse my ignorance; I am very new to Power BI and DAX and am just learning the ropes.
I have a model which has one table with a number of measures and dates. This table is like an accumulating snapshot table in Kimball's dimensional modelling; it models a process which has a series of steps and at each step we record a date.
This tables links to a date table via these different dates; in power bi one is active while the others are inactive.
I am trying to create a measure which performs a cumulative total using an inactive relationship to the date and I am unsure how this is possible to do.
I have done a cumulative total using an active relationship before like below:
Total Cumulative Scheduled By Date =
CALCULATE (
SUM ( SDCCN_ENROL_ANALYSIS_TEST[IS_SCH_ENROLMENT] ),
FILTER (
ALLSELECTED ( SDCCN_DIM_DATE ),
SDCCN_DIM_DATE[DATE_NAME] <= MAX ( SDCCN_DIM_DATE[DATE_NAME] )
)
)
I have also done creating measures across inactive measures using the USERELATIONSHIP function like below:
Total Enrolments =
CALCULATE (
SUM(SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT]),
USERELATIONSHIP(
SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],
SDCCN_DIM_DATE[SDCCN_DIM_DATE_ID]
)
)
However I have no idea how to combine them. I believe if I wrote the following then the FILTER function would just use the active relationship.
Total Cumulative Enrolment By Date =
CALCULATE (
SUM ( SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT] ),
USERELATIONSHIP (
SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],
SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]
),
FILTER (
ALLSELECTED (SDCCN_DIM_DATE),
SDCCN_DIM_DATE[DATE_NAME] <= MAX( SDCCN_DIM_DATE[DATE_NAME])
)
If anyone has any advice it would be much appreciated!
Thanks,
Alex
Solved! Go to Solution.
In your scenario, DATE_NAME column is Date type and SDCCN_DIM_DATE_ID is whole number type, also you create inactive relationship as shown in the following screenshot, right?
If so, please change your DAX to the following:
Total Cumulative by inactive relationship = CALCULATE(SUM(SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT]),USERELATIONSHIP(SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),FILTER(ALLSELECTED(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID] <= MAX(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID])))
Then we can get the result what we want. Obviously, it is different with the active one.
For more information, please check the pbix as attached.
Regards,
Lydia
In your scenario, DATE_NAME column is Date type and SDCCN_DIM_DATE_ID is whole number type, also you create inactive relationship as shown in the following screenshot, right?
If so, please change your DAX to the following:
Total Cumulative by inactive relationship = CALCULATE(SUM(SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT]),USERELATIONSHIP(SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),FILTER(ALLSELECTED(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID] <= MAX(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID])))
Then we can get the result what we want. Obviously, it is different with the active one.
For more information, please check the pbix as attached.
Regards,
Lydia
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |