topic Re: Measure calculating inaccurately in DAX Commands and Tips

Measure calculating inaccurately

AussieD — Tue, 12 Mar 2024 18:06:41 GMT

Issue:

I'm using a very simple measure and getting a non-accurate result:

in theory COUNTAX should return 24 since there are 4 zeros among the 28 rows. however in the card on the left you can see that I'm getting 28. i really just want to count the 1's (hits) but can't seem to get any of the aggragate functions to work. pictured above is COUNTAX but i have similar issues with any of the Aggregation functions.

My end goal is for "2024 goal" to be a measure of success rate. Dividing the "Hits"=1 by the total number of logged days to get a percentage. Something like this:

2024 goal =
    DIVIDE(
        COUNTAX('Headcount', [Daily Hit]=1),
        COUNTROWS(Headcount),
        0
    )

But because of the issue described above, this always evaluates to 1.

Background information:

Things to know:
1. "Daily Hit" is a calculated column and is working correctly, it assigns either a 1 or a 0 depending on whether or not a technician did enough work for the day relative to their recorded hours.

Daily Hit = 

Var workload = CALCULATE(

    SUM('Daily WOTT'[Hours/ Packet action]),

    FILTER('Daily WOTT', 'Daily WOTT'[Technician]=[Technician])

)

Return if(workload>=([Hours]-.5) && [hours]<>0, 1, 0)

2. two of my tables are related through the date and additionally through a technician table that only contains names so that it can be a 1:* cardinality.

So my question is: what else effects Dax Measures that causes abbarent results?

From what i can tell i have everything set up properly and I've formatted things correctly, so i'd like to know what the issue is here, but also what i can check in the future besides the relationships and Dax Libraries.

Appreciate any help i can get, Thanks 😁

Re: Measure calculating inaccurately

Greg_Deckler — Tue, 12 Mar 2024 18:32:20 GMT

AussieD Can you just use COUNTROWS( FILTER( ... [something] = 1)) ?

Re: Measure calculating inaccurately

AussieD — Tue, 12 Mar 2024 19:50:11 GMT

Greg_Deckler That seems to work, and is the only formula that has worked so far. Thanks for the suggestion 😁

So then the only thing I'm struggling to undestand is the difference between these two functions:
COUNTROWS(FILTER('Table', [Column]=x)) this provides the correct result
COUNTAX('Table', [Column]=x) This provides an incorrect result

Shouldn't these intrisically work the same way?

Re: Measure calculating inaccurately

Greg_Deckler — Wed, 13 Mar 2024 12:14:39 GMT

AussieD Hmm. Any chance you can post sample data as text so that I can test this out?

Re: Measure calculating inaccurately

AussieD — Wed, 13 Mar 2024 13:53:42 GMT

Greg_Deckler sorry, all of my data sets are live, i can't share that level of data. But, I agree, this has been making me want to find another test case. This seemed like a really weird issue from the start and the solution seems no more intuitivly correct than the alternatives.

Appreciate your help so far.

Re: Measure calculating inaccurately

Greg_Deckler — Wed, 13 Mar 2024 15:39:27 GMT

AussieD Only difference I can think of is that COUNTA supports boolean values. That's the only real difference between it and COUNT