DAX - Count all rows from a dimension table with values below a max in a fact table

[FabianB]

Hi,

I am trying to develop a DAX measure.

I have a dimension table (dim_machines) that contains a name column.

I have a fact table (machine_measurements), linked to the dimension table by this name column.

The fact table contains a power column and a timestamp with many power values for the different machines at different times.

I am now trying to design a measure that counts all machines that never have power values > 20.

here is what I tried so far:

InactiveCount =

 CALCULATE(COUNT(dim_machines[name]),FILTER(VALUES(dim_machines[name]),MAX(machine_measurements[power])<20))

The measure gives me a blank value. I think what is happening is that it is calculating the MAX value for the whole fact table instead of calculating it for each machine separately.

Can anyone give me a hint on how to solve this?

Many thanks!

[FabianB]

Works like a charm. Thank you so much!

[Anonymous]

You're very welcome! Glad i could help!

[Anonymous · Accepted solution]

Count Machines < 20 =
COUNTROWS(
FILTER(dim_machines,
NOT(COUNTROWS(FILTER(machine_measurements, machine_measurements[power] > 20 && machine_measurements[name] = dim_machines[name])) > 0)
)
)

Hey could you try this and let me know if it works? Thanks! 🙂