Dax Measure/Data Relationship Problem

[4thsky]

Hi Everyone,

I have a data relationship/measure question that will be easy for the experts but I have been trying for weeks to get this right and just can't seem to do it.

The Scenario: I have two Excel data sources.

The relevant data in November 2021 CAN contains Date, Company Name (Job), Work Units (QTY).

Incident List contains Date and Company.

The real files are much bigger than this but this data is the crux of my problem.

Data in November 2021 CAN represents Many companies with Work Units completed on a single day. The companies repeat due to different job sites which I have not included.

Data in Incident List represents Workplace Incidents on a single job site with a Company. So basically a Company Name equals 1 incident.

There is a formula that must be calculated between the data in these two tables for reporting purposes Per Company. It looks like this.

Simple Formula

TRIF = (Incidents * 200,000) / (QTY*14)

Actual Dax Code

(COUNT('Incident List'[Client Incidents]) * 200000) / (SUM('Monthly Ticket Labour Entries Reports'[QTY])*14)

The Problem

The problem I'm having is that I can get all of the data in the visual below to line up except for the Incident Count. The formula works, but if you check the data samples below you will see that Client Incidents is showing as ALL client incidents for November (6) being associated with each separate client. Instead of having the count relating properly to the Date selected and therefore only showing the incidents that pertain to each client.

So in the visual below it should not have 6 for each client. It should be 1 for Arc, 1 For Nuvista, 2 for Pipestone, etc.

I cannot for the life of me figure out why I can get the QTY per Company sorted properly with my Date Slicer but I cannot get the incident count to be anything other than a mass total. I probably need to expand up on the formula for Client Incident Count but everything I've tried has failed. 

Your help is appreciated. Sample data attached.

Downloadable here:  Sample Data Download 

File: November 2021 CAN

Ticket Date	Job	QTY
2021-11-01	Conoco	1.00
2021-11-01	Conoco	0.00
2021-11-01	Conoco	1.00
2021-11-01	Conoco	1.00
2021-11-01	Tourmaline	1.00
2021-11-01	Tourmaline	1.00
2021-11-01	Nuvista	1.00
2021-11-01	Nuvista	1.00
2021-11-01	Nuvista	1.00
2021-11-01	Nuvista	1.00
2021-11-01	Nuvista	1.00
2021-11-01	Petronas	1.00
2021-11-01	Petronas	1.00
2021-11-01	Petronas	1.00

File: Incident List

Date	Company
2021-11-01	NuVista
2021-11-06	Arc
2021-11-07	Petronas
2021-11-14	Conoco
2021-11-22	Pipestone
2021-11-29	Pipestone

Table Relationships

[4thsky]

This solution worked perfectly, thanks again!

[4thsky]

Amazing. It took me an hour to write out the problem and you may have solved it in 5 minutes! I will try this tomorrow and report back. Thanks!

[jdbuchanan71 · Accepted solution]

@4thsky 

I think what you are running into is what ever table you are pulling your company name from not filtering the other table.  What you need is a company table that sits over both of of your fact tables (the same way the date table does).  You can create one in DAX that will always stay up to date as new companies are added to the model like this.

Companies =
DISTINCT (
    UNION (
        DISTINCT ( 'November 2021 CAN'[Job] ),
        DISTINCT ( 'Incident List'[Company] )
    )
)

Then you link this new companies table to both of the fact tables and pull the company name from this table.  That filter will flow down to both of the tables and your measure should work.