Measure returning incorrect value

[tananich]

Hi all,

I have a strange issue, could someone please  help me with that.

I have a grid as in the picture below

I need to distinct count of employees with "Overreported in column "Test3" (negative value in column 'project gap")

I have a measure "Overreported Ppl" =  CALCULATE(Distinctcount ('Table_Name'[Employee]), FILTER( 'Table_Name', 'Table_Name[Project Gap]<0))

However it returns me employees with positive numbers in 'project gap' column and who are marked as  'Underreported' in test3 column.

I cannot figure this out how come I get something like that? 

Thank you

[tananich]

@v-chuncz-msft

thank you, that works!

[v-chuncz-msft · Accepted solution]

@tananich,

You may try the following measure.

Measure =
COUNTROWS (
    FILTER (
        VALUES ( 'Table_Name'[Employee] ),
        CALCULATE ( SUM ( 'Table_Name'[Project Gap] ) < 0 )
    )
)

[BobBI]

Hi  ,

you this DAX , this will count emplyee where pr_gap has -ve value.

Overreported People = CALCULATE(DISTINCTCOUNT(valueTest[Employee]),
	                        FILTER(valueTest,
        	                       SIGN(valueTest[Pr_gap])=-1
                	               )
                               )

Thanks,

SS

[tananich]

Hi @BobBI

Thanks for reply, I used exactly your formula however the same result see below, this is new column called test overrep ppl

 

[rafaelmpsantos]

Try this

"Overreported Ppl" =  CALCULATE(Distinctcount ('Table_Name'[Employee]), FILTER( All('Table_Name'), 'Table_Name[Project Gap]<0))

if it solve please mark as solved, and give me your positive feedback.

[tananich]

Hi @rafaelmpsantos

thanks for reply, unfortunately that doesn't work, i need to count employees and this formula returns completely different value