topic Re: A measure using IF condition with a column reference in DAX Commands and Tips

A measure using IF condition with a column reference

kmartink — Tue, 28 Mar 2023 15:08:10 GMT

Hi, I'm using a slicer filtering the weeks and have made this measure to calculate how many days in a selected week was a score below 80%.

Low Score =

IF(

CALCULATE(SUM('Table'[Score]))>=0.8,

CALCULATE(DISTINCTCOUNT('Table'[Weekday]),FILTER('Table','Table'[Score]<0.8)))

However, the measure always returns 0 even though there were some days with a score below 80%. How can I fix it?

Thank you for your help.

Re: A measure using IF condition with a column reference

tamerj1 — Tue, 28 Mar 2023 17:46:36 GMT

Hi kmartink

make sure you have created a measure not a calculated column by mistake

Re: A measure using IF condition with a column reference

kmartink — Wed, 29 Mar 2023 07:04:00 GMT

Hi tamerj1,

yes what I've created is a measure. I was wondering if the problem might be caused either by that the formula accidentally summarizes all the rows of the "Score" column which would mean the result would be always higher than 80% and so the zero will be displayed? Or either that my "Score" has the Data type "decimal number" and the format "percentage" but the formula uses "0.8" for 80%?

Is there any other way how to rewrite the formula so it might not be accidentally summing up all the rows of the column "Score" if that's the case what's happening right now? I already tried to use "AVERAGE" instead of "SUM" but there wasn't any difference. Thanks.

Re: A measure using IF condition with a column reference

tamerj1 — Wed, 29 Mar 2023 07:45:10 GMT

kmartink
Would you please present sample fo the data and perhaps a screenshot of the visual?

Re: A measure using IF condition with a column reference

kmartink — Wed, 29 Mar 2023 12:36:12 GMT

tamerj1

Here is my data sample of the Score:

And this is the visual:

Re: A measure using IF condition with a column reference

tamerj1 — Wed, 29 Mar 2023 13:12:58 GMT

kmartink

I guess you are just trying to have in a card visual the count of the days that have scores less than 0.8?

if this is the case please try

Low Score =
SUMX (
DISTINCTCOUNT ( 'Table'[Date] ),
IF ( CALCULATE ( SUM ( 'Table'[Score] ) ) < 0.8, 1 )
)

Re: A measure using IF condition with a column reference

kmartink — Thu, 30 Mar 2023 09:40:18 GMT

Hi tamerj1,

this is the error I get when I use your formula: The SUMX function expects a table expression for argument '1', but a string or numeric expression was used.

Re: A measure using IF condition with a column reference

tamerj1 — Thu, 30 Mar 2023 09:46:02 GMT

kmartink
Apologies, my mistake

Low Score = SUMX ( DISTINCT ( 'Table'[Date] ), IF ( CALCULATE ( SUM ( 'Table'[Score] ) ) < 0.8, 1 ) )