Forum Discussion
Cumulative Totals using inactive relationship
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
- Anonymous8 years ago
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
1 Reply
- AnonymousNot applicable
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