topic Re: Count if measure returns true - error with total value in DAX Commands and Tips

Count if measure returns true - error with total value

Anonymous — Tue, 04 Jan 2022 16:45:32 GMT

Hi,

I try to count how many documents have a final cost amount >= 500euro.
For 10 nov 2020 this should return 7.

I created a measure that returns a correct value per row (see table in screenshot).

But the total value is wrong. It says 279 (which is the count of all rows) instead of 7.

I tried different measures but nothing is working....
In the above screenshoted table the VAR Test1 is applied.

Anyone knows how I need to modify the measure?

Julie

Re: Count if measure returns true - error with total value

smpa01 — Tue, 04 Jan 2022 16:50:18 GMT

Anonymous is it kindly possible to provide a sample pbix?

Re: Count if measure returns true - error with total value

ValtteriN — Tue, 04 Jan 2022 17:20:29 GMT

Hi,

I think the problem is with your [Final cost amount] measure. I suppose it return more than 500 to all rows.

Now if you use this as your condition in test1 it will return true to all rows since the sum is greater. It seems you use a column instead of your measure in your table. To create the condition measure you can use something like:

Final cost Amount test = SELECTEDVALUE('Table'[Final_cost_amount])

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: Count if measure returns true - error with total value

v-yanjiang-msft — Fri, 07 Jan 2022 06:50:41 GMT

Hi Anonymous ,

According to your description, here's my solution.

You can modify the measure like this:

Final Cost Amount>500euro = VAR _T = ADDCOLUMNS ( 'FactControlReportsALL', "Requirment", IF ( [Final_Cost_Amount] >= 500, 1, BLANK () ) ) RETURN SUMX ( _T, [Requirment] )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count if measure returns true - error with total value

Anonymous — Fri, 07 Jan 2022 07:42:17 GMT

Hi v-yanjiang-msft , This solution does not work unfortunantely.
For precision: in my data one document number can have several rows with different articles.
In the below table the column document_number is a distinct count.

Re: Count if measure returns true - error with total value

Anonymous — Fri, 07 Jan 2022 07:58:22 GMT

Hi ValtteriN , indeed the VAR Requirement refers to a measure. Sorry forgot to precise this.

[Final Cost Amount] = SUM(FactControlReportsALL[Final_Cost_Amount])

I tried to use SELECTEDVALUE.

Created VAR Requirement2 and used this in VAR Test1. But it does not behave as expected.

One document number can have several rows in the data (different articles with the same document number). It seems that this measure returns 1 when the final cost amount is >= 500 for one article/row. It should return 1 if the final cost amount is >= 500 for the document number (so sum of final cost amount of the different rows with same document number).
Also the total value is blank 😕

Re: Count if measure returns true - error with total value

v-yanjiang-msft — Fri, 07 Jan 2022 08:02:21 GMT

Hi Anonymous ,

Try to modify the measure like this:

Final Cost Amount>500euro = VAR _T = ADDCOLUMNS ( DISTINCT('FactControlReportsALL'[Document_Number]), "Requirment", IF ( [Final_Cost_Amount] >= 500, 1, BLANK () ) ) RETURN SUMX ( _T,[Requirment] )

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count if measure returns true - error with total value

Anonymous — Fri, 07 Jan 2022 08:45:52 GMT

Hi v-yanjiang-msft , it is still not working.
The row values are now correct, but the total value is wrong.

Re: Count if measure returns true - error with total value

v-yanjiang-msft — Fri, 07 Jan 2022 08:55:09 GMT

Hi Anonymous ,

Isn't "Final Cost Amount" a measure? It seems a measure in your original post.

Best Regards,
Community Support Team _ kalyj

Re: Count if measure returns true - error with total value

Anonymous — Fri, 07 Jan 2022 09:01:31 GMT

Yes [Final Cost Amount] was a measure.

[Final Cost Amount] = SUM(FactControlReportsALL[Final_Cost_Amount])

I wrote it out in the 'final cost amount >= 500' measure instead of refering to the 'Final Cost Amount' measure (which simply is a sum of final cost amount). This to avoid confusion

Re: Count if measure returns true - error with total value

v-yanjiang-msft — Fri, 07 Jan 2022 09:12:41 GMT

Hi Anonymous ,

In the formula, try to use the measure [Final Cost Amount] directly, but not SUM(FactControlReportsALL[Final_Cost_Amount])

Best Regards,
Community Support Team _ kalyj

Re: Count if measure returns true - error with total value

Anonymous — Fri, 07 Jan 2022 09:28:27 GMT

It worked ! Thanks ! 🤗

But I don't understand what is the difference between refering to another measure (which works) and includind the calculation in the measure itself (not working)?

As in both cases it is the same calculation? Could you explain?

Re: Count if measure returns true - error with total value

v-yanjiang-msft — Fri, 07 Jan 2022 09:43:19 GMT

Hi Anonymous ,

It's my pleasure! In this formula maybe because the ADDCOLUMN function, it means create a new table in the measure, you can refer to this artical.

Reference:ADDCOLUMNS function (DAX) - DAX | Microsoft Docs

Best Regards,
Community Support Team _ kalyj