Count rows of the values of a measure

[Leyli_ON]

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.

[Leyli_ON]

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

[Fowmy]

@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"
    )
)

[Leyli_ON · Accepted solution]

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

[Leyli_ON]

Not working, need distinc counts