Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BM4291
Resolver I
Resolver I

Ignoring Relationships

Hi,

 

Having a bit of difficulty nailing down a formula and hoping for some help.

 

I have three tables in a relationship:

 

Case - Represents a case of work

Report - Represents reports associate with cases

Date - Date Dim

 

I'm trying to show how many reports have been completed on each closed case and continually falling short.

 

My data looks like the below; Date is a standard date dimension with multiple columns i.e. Date and MonthYear (MMM-YY). Case links to Report and Date with active relationships; Report links to date with inactive relationships.

 

Case

 

IDOpenCaseClosureDate
123 22/01/2019
4561 
789 24/01/2019

 

Report

IDCaseIDCompletedCompletedDate
1123101/01/2019
2123105/04/2012
3123103/01/2019
4456104/01/2019
5456  
6789101/01/2019

 

According to the above, I know that 2 cases closed in Jan-19; I can prove this with

CasesCount = DISTINCTCOUNT('Case'[ID])
closedCases = CALCULATE([CasesCount], ISBLANK('Case'[OpenCase]))

 

I then want to find the number of reports associated with that case; I don't care when they were completed, I just need to know that they exist on that specific case.

 

When I look at the case level I get the right answer but once a date value is brought in, the figures are wrong and I'm shown closed cases (correct) but reports filed in that period (incorrect), my question is how can I ignore any date relationships with Reports and show a count of reports for all cases closed in that period.

 

I would expect to see something like the below but instead I'm seeing 3 reports because one of those was completed in a different month.

 

MonthYearCases ClosedReports
Jan-1924

 

Most recently I've tried this but returning blank; I'm trying to say return the count of reports for any cases that closed during the time period.

start = MIN(Dates[Date])
end = MAX(Dates[Date])
reports = DISTINCTCOUNT(Reports[ID]) closedCasesReports = CALCULATE([reports], FILTER('Case', 'Case'[ClosureDate] >= [start] && 'Case'[ClosureDate] <= [end] ) )

I've tried quite a lot of iterations with varying success but it appears that whenever the date is brought into the equation results return incorrectly... Can I ignore a date relationship altogether?

 

Any help greatly appreciated.

1 ACCEPTED SOLUTION

@BM4291 here is updated pbix, added another case without report and showing some calculations, not sure if that is what you are looking for.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

10 REPLIES 10
parry2k
Super User
Super User

@BM4291 not sure why you are getting different count since relationship between reports and calendar dimension is inactive. I just put together the solution based on your data and everything looks ok to me. see attached.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I think I may have complicated things a bit for myself and then gone in a bit too deep... Have reverted back to something similar to what you're using (my actual solution is a bit more complext but can't share org. data).

 

If all works as expected, will flag as solution.

@parry2k - Yes, I was definitely overcomplicating... This gets me my base measure.

 

The next challenge was pointing out where a case has atleast one report but keep coming unstuck or getting blank values... Any suggestions.

 

I've tried a few variations of CALCULATE and FILTER but the results are coming back far too high; is it because I need to repeat all of the additional conditions I have?

@BM4291 glad it is working out and you are getting there. I don;t think # of reports for a case matters, may be I'm missing something. If you can share use case with example data, will help to understand the problem/issue.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k - Using the above example, say that there was a 3rd closed case for January but it had no records in the report table.

 

You'd end up with something like the below:

 

MonthCasesReportsCases with Reports%
Jan-1834266.7%

 

I'm looking to find the % of cases that have atleast 1 report on them.

@BM4291 isn't it your current image "cases with report" is showing 2 even 3 cases are closed and 1 doesn't have report. Not sure where the issue is? 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k - Yeah, so what I'm saying is, I have the cases measure, I have the reports measure all sorted; what I want to calculate is where a case has at least one report and use that to get a %.

 

I know this returns an error but what I'm effectively doing is:

Measure = CALCULATE([Cases], [Reports] > 0)
Measure1 = CALULCATE([Cases], NOT(ISBLANK([Reports]))

Have just seen your other reply and you could be onto something there... Counting the cases that exist in the report table that match my criteria... Will get back to you!

 

@BM4291 well there is many way to achieve this but in my latest updated pbix file it is already sorted out.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k thank you, you've been really helpful on this; getting the final measures sorted now amidst the different filters I need.

 

Will flag as solution, really appreciate the help!

@BM4291 here is updated pbix, added another case without report and showing some calculations, not sure if that is what you are looking for.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.