Forum Discussion

FabianB's avatar
FabianB
Frequent Visitor
3 years ago
Solved

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

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!
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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! 🙂 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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! 🙂 

  • FabianB's avatar
    FabianB
    Frequent Visitor
    3 years ago

    Works like a charm. Thank you so much!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      You're very welcome! Glad i could help!