topic Re: DAX Measure Count, group by, Filter. in DAX Commands and Tips

DAX Measure Count, group by, Filter.

Anonymous — Tue, 22 Aug 2023 05:40:56 GMT

Hello everyone,

I'm trying to write a meaure to Count the number of Puchase Contract if the Total Contract Consumptoin% exceeds 80% of the contract value.

Note: The Puchase Contract could have multiple items but i need the measure to count the Puchase Contract and apply the condition on the Contract Consumptoin% of total items per Puchase Contract (the percentage of the summed items).

Table= Contract

I would really apprecaite your support.

Thanks in advance.

Re: DAX Measure Count, group by, Filter.

JoeBarry — Tue, 22 Aug 2023 09:24:54 GMT

Hi Anonymous

You can try this measure

Exceed 80% = CALCULATE(COUNT(Contract[Purchase Contract]), KEEPFILTERS([Contract Consumption%]>= 0.8)

Thanks

Joe

Re: DAX Measure Count, group by, Filter.

Anonymous — Wed, 23 Aug 2023 06:18:31 GMT

Thanks alot Joe.

Exceed 80% = CALCULATE(COUNT(Contract[Purchase Contract]), FILTER(Contract,[Contract Consumption]>= 0.8))

I've tried the above measure but it will calculate the count on line item level. The Puchase Contracts have multiple items so i need the logic of the measure to calculate (Count) for the summed values for each Puchase Contract. (by grouping by Puchase Contract if possible)

Please let me know if you need more illustration.

Thanks again for your support

Re: DAX Measure Count, group by, Filter.

JoeBarry — Wed, 23 Aug 2023 07:44:05 GMT

Hi Anonymous

Can you show me in a screenshot your expected outcome? Maybe create a table within your comment. And maybe if the data isn't sensitive a screenshot of a few lines of the table?

You can also try below. Presuming there is a Value in each Row, this will count all rows, therefore checking each consumption is over 80%

Exceed 80% = CALCULATE(COUNTROWS(Contract]), KEEPFILTERS([Contract Consumption%]>= 0.8)

If you remove the Released Value from the Rows also after creating the measure from your Matrix, it should show you what you need.