DAX measure help: how to get count that "ignores" relationship?

Question

I'm keeping track of machines & their on/off status, and their service history:

MachineHistory
MachID
Branch
Status
StatusDate

ServiceHistory
ServiceID
MachID
ServiceDate

Calendar
Date
Year
Month

MachineHistory and ServiceHistory are related on MachId. Calendar is related to both on the date cols.

What I want is measures for ActiveMachineCount (means Status = "ON") and ServicedMachineCount (means row exists in ServiceHistory table). Both should be group-able by date & branch.

So I should be able to have an excel pivot that shows eg

Branch     ActiveMachCnt     ServicedMachCnt
A             50                  13
B             23                   6

I have a measure of MachIdCnt = distinctcount(machineid) My problem is with ActiveMachIdCnt = calculate([MachIdCnt], MachineHistory[Status] = "ON")

No matter how I try to fiddle with ActiveMachIdCnt, I either get a) literally ALL of the machine id's in the table, or b) a count of machine id's that have been serviced (due to relationship).

I'm clearly not understanding the ALL, ALLEXCEPT, etc family of functions correctly. :/

How can I get the ActiveMachIdCnt measure to "ignore" the relationship to ServiceHistory, but still respect filters on date & branch?

Thanks for any tips, sff

RADO RADO · Accepted Answer · 2018-04-07T19:05:22

I think you are having this problem because your data model is incorrect. Your history tables (machine history and service history) BOTH are fact tables. Fact tables can not have direct relations, it's prohibited.

What you need to do is to add a dimension "Machine", which will contain MachineId and other usefull attributes of your machines. This table then will be related to Machine history and Service history via MachineID, exactly the same way as your Calendar table is related to them via Date.

With such structure, your DAX is very simple:

Active Machines = DISTINCTCOUNT(MachineHistory[MachineId])
Serviced Machines = DISCTINCTCOUNT(ServiceHistory[MachineId])

and if you want to analyze these measures by Branch, you will need to have branch either as a separate dimension (that would be my choice), or as an attribute in the dimension "Machine" (which would work but conceptually is an inferior design).

DAX measure help: how to get count that "ignores" relationship?

1 Answers