Ambiguous paths when linking to date table / How to count rows containing a particular value

[swisdom]

So I have a dataset that contains a list of problems, some information about the problem, and a date the problem was submitted / fixed. I have a Measure calculating the difference between the fix date & the submit date, to determine how long the problem was in process, and assigning it a "bucket" value based on this calculation (<5 days, 5-10 days, 10+ days, etc). I also have a Date Table which has model link to the Submitted date.

Example Data:

Submitted Date	Fixed Date	Days in Field	Time in Field
1/1/21	1/3/21	2	<5 Days
2/1/21	2/7/21	6	5-10 Days
3/1/21	3/4/21	3	<5 Days
4/1/21	4/16/21	15	10+ Days

I would like to create a count of problems within each "bucket" - which I was able to do using this code:

Bucket1 = calculate(COUNTX(filter('Table1','Table1'[Time in Field]="<5 Days"),'Table1'[Time in Field]))

This created a separate measure for each bucket and a tally of each instance. (If there is a better way to go about this, please let me know)

Then, I want chart the number of, say, <5 Day fixes that occured over time. I can do that if I use the Submitted Date directly from the dataset, but if I use the Date table instead it gives me the following error:

"Couldnt load the data for this visual. There are ambiguous paths between..."

Any ideas for a way to solve this?

[CNENFRNL · Accepted solution]

[TheoC]

This is next level!

[swisdom]

Interesting! So the way that I set up the Time in Field calculation was like this:

Time in Field = 
SWITCH(
    TRUE(),
    MIN('NCR Raw DL'[DAYSINFIELD])<0, "Exclude",
    MIN('NCR Raw DL'[DAYSINFIELD]) <5, "<5 Day",
    MIN('NCR Raw DL'[DAYSINFIELD])<10, "5-10 Day",
    "10+ Day"
    )

I can try and set it up with a reference table like you have, though I'm not sure that would solve my inability to link it to the Date table? I'll give it a try.

EDIT TO ADD: It worked! I'm not really sure why, but it lets me link to the Date table now. I'll accept your solution, but if you feel up to it, I'd love a brief explanation if possible?

[TheoC]

Hi @swisdom 

Are you able to share a screenshot of your Model / Relationships view? It is very likely the error is stemming from established relationships.

Also, in terms of the grouping, you can use "Conditional Columns" in Power Query which reduces the volume of measures that you require and will achieve the same outcome.  Let me know if you'd like me to run you through simple steps for this 🙂

Theo

[swisdom]

I am actually not able to edit the dataset/columns in PowerQuery, as it is linked from an outside source via DirectQuery. This is why I am using Measures for all the calculations 🙂

This is the portion of the model linking the data table in question to the Date table, with the active link highlighted. (The others are inactive links to other dates in the table for other calculations using [USERELATIONSHIP] later, though they are not set up yet.)

The Date table also links to the Shipping dataset just above it in the same way. There are also links to other reference tables (customer, failure type, location, etc) but nothing that is causing circular logic that I can see.

[TheoC]

@swisdom thank you for sharing.  It's really interesting.  I am unsure to be honest. Although a clear solution has been provided to get you the outcome required, I am interested to better understand the cause of the error otherwise, as you expand / enhance your report, there is a potential likelihood that it may repeat itself if not identified / corrected.