Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Icon for Impactful Individual rankImpactful Individual
1 year ago
Solved

measure 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]), ...
Need Help
  • Pikachu-Power's avatar
    Pikachu-Power
    1 year ago

    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.

sanalytics's avatar
sanalytics
Icon for Super User rankSuper User
1 year ago

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