topic Re: Count rows of the values of a measure in DAX Commands and Tips

Count rows of the values of a measure

Leyli_ON — Fri, 24 Jan 2025 19:45:21 GMT

Hi All,

I tried looking at various posts here to see if someone had similiar ask, but coudnt find one that really fits, but if its something i missed please refer me to the post happy to review it.

Herewith my query: I have created a Measure as follows:

OffMarc_Cost =

CALCULATE(SUM('Hana Order Acutal Cost'[Cost]),'Hana Order Acutal Cost'[Cost Element] <> "0000555090",'Hana Equipment Additional Data'[Equipment F/L] = "1200-MO-0215-M020-001")

OnMarc_Cost =

CALCULATE(SUM('Hana Order Acutal Cost'[Cost]),'Hana Order Acutal Cost'[Cost Element] = "0000555090",'Hana Equipment Additional Data'[Equipment F/L] = "1200-MO-0215-M020-001")

This sum all the cost of a specific piece of equipment that falls into these specific filter.

I want to be able to create a rowcount from the result of these measure as per below example. How will i create this in a measure, as i will use these totals for a month then in a calculation.

Re: Count rows of the values of a measure

Fowmy — Fri, 24 Jan 2025 19:59:31 GMT

Leyli_ON

You can try this way:

Count_OffMarc_Rows = COUNTROWS( FILTER( 'Hana Order Acutal Cost', 'Hana Order Acutal Cost'[Cost Element] <> "0000555090" && RELATED('Hana Equipment Additional Data'[Equipment F/L]) = "1200-MO-0215-M020-001" ) )

Re: Count rows of the values of a measure

Leyli_ON — Mon, 27 Jan 2025 13:31:10 GMT

Hi Fowmy

Thanks for your response, however this I have tried but it seems to count all the entries for this, and i need the unique count for the equipment. So i have a measure to do unique count on the equipment as that is a link to another table.

Count_OffMarc_Rows =

COUNTROWS(SUMMARIZE('Hana Equipment Additional Data','Hana Equipment Additional Data'[Equipment Desc])

)

As your measure do a count of each row based on those filter which is correct, but i need the distinc count... As that total should then be 15 for May

Re: Count rows of the values of a measure

Leyli_ON — Mon, 27 Jan 2025 18:43:33 GMT

Not working, need distinc counts

Re: Count rows of the values of a measure

Leyli_ON — Mon, 27 Jan 2025 20:16:28 GMT

Ok, i tweeked it a bit, since I was working with 2 tables. 1 table was Equipment which i used to display the equipment related to my Cost which was in the HanaOrderActualCost Table. So i just brought the equipment column into my HanaOrderACtualCost table since it was related and modified the measure as follows:

OffMarcCount = CALCULATE(DISTINCTCOUNT('Hana Order Acutal Cost'[Equipment]),FILTER(

'Hana Order Acutal Cost',

'Hana Order Acutal Cost'[Cost Element] <> "0000555090"

&& RELATED('Hana Equipment Additional Data'[Equipment F/L]) = "1200-MO-0215-M020-001"

))

Would still like to know if it is possible to write a dax measure without bringing in the equipment in my ordertable to achieve the same