Forum Discussion

Zbig62's avatar
Zbig62
Regular Visitor
2 years ago
Solved

problem with calculating the average using variables defining a range of values

Hello I am a beginner DAX user and I have a serious problem with calculating the average using variables defining a range of values. The problem is as follows: Table: 'public Measurement' 1. aver...
  • Zbig62's avatar
    Zbig62
    2 years ago

    welcome back
    Thank you for your suggestions, it allowed me to locate the problem.
    Instead of count() I used the countx() function and it works very well
    thank you again

     

    var stop_av = CALCULATE(
        COUNTX('public Measurement';'public Measurement'[number]);
        FILTER(
            ALLSELECTED('public Measurement');
            'public Measurement'[characteristicId] = MAX('public Measurement'[characteristicId])
        )
    )
Zbig62's avatar
Zbig62
Regular Visitor
2 years ago

welcome back
Thank you for your suggestions, it allowed me to locate the problem.
Instead of count() I used the countx() function and it works very well
thank you again

 

var stop_av = CALCULATE(
    COUNTX('public Measurement';'public Measurement'[number]);
    FILTER(
        ALLSELECTED('public Measurement');
        'public Measurement'[characteristicId] = MAX('public Measurement'[characteristicId])
    )
)
tamerj1's avatar
tamerj1
Community Champion
2 years ago

Zbig62 

There shouldn't be any difference between COUNT and COUNTX. But your new solution made me understand that the number-Characteristicid combination in the source table is unique. 

 

Also COUNT/COUNTX should work properly only if [number] restarts from 1 for each characteristicid. 
note: the 'public Measurement'[number] >= start_av a gument is not needed in all cases.