Forum Discussion
Impactful Individualmeasure relative value dont work
Hi all,
i have the following dax code where i try to calculate a relative value in a table:
Measure_count_entry_relative:
VAR CurrentCount = CALCULATE(COUNT(MEMBER[ENTRYDATE]),
FILTER(MEMBER, YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) &&
MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth])),
MEMBER[AGE_ENTRYDATE] >= 18 && MEMBER[AGE_ENTRYDATE] <= 94)
VAR TotalCount = CALCULATE(COUNT(MEMBER[ENTRYDATE]),
FILTER(MEMBER, YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) &&
MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth])),
MEMBER[AGE_ENTRYDATE] >= 18 && MEMBER[AGE_ENTRYDATE] <= 94,
ALL(MEMBER[CLUSTER_ENTRYDATE]))
RETURN
DIVIDE(CurrentCount, TotalCount, 0)
But i get 100% instead of the relative value:
I use ALL(MEMBER[CLUSTER_ENTRYDATE]) to ignore the rows CLUSTER_ENTRYDATE but it seems not to work in this case. In other measures it worked normally. Does someone see why this dont work??
Many thanks!
Best regards
Pikachu-Power
One way it seems to work. When i create a new table
FILTER( MEMBER, YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) && MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth])and remove this part from the measure than i get the right values. So the error must be here somewhere.
4 Replies
- bhanu_gautam
Super User
Pikachu-Power , Try using
Measure_count_entry_relative :=
VAR CurrentCount = CALCULATE(
COUNT(MEMBER[ENTRYDATE]),
FILTER(
MEMBER,
YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) &&
MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth])
),
MEMBER[AGE_ENTRYDATE] >= 18 && MEMBER[AGE_ENTRYDATE] <= 94
)VAR TotalCount = CALCULATE(
COUNT(MEMBER[ENTRYDATE]),
FILTER(
MEMBER,
YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) &&
MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth])
),
MEMBER[AGE_ENTRYDATE] >= 18 && MEMBER[AGE_ENTRYDATE] <= 94,
REMOVEFILTERS(MEMBER[CLUSTER_ENTRYDATE])
)RETURN
DIVIDE(CurrentCount, TotalCount, 0) 
sanalyticsReplace ALL(MEMBER[CLUSTER_ENTRYDATE]) with REMOVEFILTERS(MEMBER[CLUSTER_ENTRYDATE]) to remove the filter more effectively while calculating the total count.
Use the below DAX
Measure_count_entry_relative := DIVIDE( CALCULATE( COUNT(MEMBER[ENTRYDATE]), FILTER( MEMBER, YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) && MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth]) ), MEMBER[AGE_ENTRYDATE] >= 18 && MEMBER[AGE_ENTRYDATE] <= 94 ), CALCULATE( COUNT(MEMBER[ENTRYDATE]), FILTER( MEMBER, YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) && MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth]) ), MEMBER[AGE_ENTRYDATE] >= 18 && MEMBER[AGE_ENTRYDATE] <= 94, REMOVEFILTERS(MEMBER[CLUSTER_ENTRYDATE]) ), 0 )if it does not work. I suggest you to provide some dummy data with your pbix file to test.
Regards
sanalytics
If it is your solution then please like and accept it as solution
Pikachu-PowerStill 100% when i use REMOVEFILTERS. No change. I work with real data and no dummy data.
The years in the column come from the calender table which is connected with MEMBER[ENTRYDATE]. I dont know if there is maybe a problem.
- Pikachu-Power
One way it seems to work. When i create a new table
FILTER( MEMBER, YEAR(MEMBER[ENTRYDATE]) = YEAR(MEMBER[date_endofmonth]) && MONTH(MEMBER[ENTRYDATE]) = MONTH(MEMBER[date_endofmonth])and remove this part from the measure than i get the right values. So the error must be here somewhere.
