measure relative value dont work

[Pikachu-Power]

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

[Pikachu-Power · Accepted solution]

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.

[Pikachu-Power]

Still 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.

[sanalytics]

@Pikachu-Power 

Replace 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

[bhanu_gautam]

@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)