Calculated Measure that filters across 2 tables using an inactive relationship

[tyezza]

I hope one of you wizards can help me out.  I just can’t figure out how to DAX this up.

I need to write a calculated measure that gives me the Open Cases with Open Follow Up Requirements that are due today.  If we were running on 9/29 the total would be 2, not 3, because 2 of the open follow ups are for the same case.

My data looks like this and I have an inactive relationship between these tables on CaseId.

Data

Any direction would be very much appreciated.

Thanks all.

[tyezza]

Thank you dilumd and Ashish.

Dilumd, that’s close but the value I need is two. I need the count of open cases, with open follow up requirements due today.

I need to filter cases based upon their status and whether or not they have an open requirement. I do not need the count of open requirements.

Thanks again. Your time is very much appreciated.

[dilumd]

Hi

Based on your above data, can you show me (in an excel sheet). what kind of outcome your looking at?

Coz I’m sorry! Still I couldn’t get you properly.

[tyezza]

Thanks again for the help. If I was going to write is in tsql it would look like this.

Select count(0) as OpenCasesDueToday
From cases c
Where c.[case status] =‘open’
And exists (select 1
from requirements r
Where r.type = ‘Follow Up’
And r.status = ‘open’
And r.DueDate = convert(date, getdate())
And c. caseid = r.caseid
)

It would find case number 1 and 2. The desired result would be a count of 2 cases. Keep in mind I’m for purposes of this post I’m assuming getdate will return 9/29/2017.

Really appreciate your help.

[Eric_Zhang · Accepted solution]

---

@tyezza wrote:
Thanks again for the help. If I was going to write is in tsql it would look like this.

Select count(0) as OpenCasesDueToday
From cases c
Where c.[case status] =‘open’
And exists (select 1
from requirements r
Where r.type = ‘Follow Up’
And r.status = ‘open’
And r.DueDate = convert(date, getdate())
And c. caseid = r.caseid
)

It would find case number 1 and 2. The desired result would be a count of 2 cases. Keep in mind I’m for purposes of this post I’m assuming getdate will return 9/29/2017.

Really appreciate your help.

---

@tyezza

The DAX equivalent to the given SQL is like below. You could test it by replacing TODAY() with a given date, say DATE(2017,9,29).

Measure =
COUNTROWS (
    FILTER (
        'Case',
        'Case'[Case Status] = "open"
            && CONTAINS (
                FILTER (
                    Requirements,
                    Requirements[Type] = "Follow up"
                        && Requirements[Status] = "open"
                        && Requirements[DueDate] = TODAY()
                ),
                Requirements[Caseid], 'Case'[CaseId]
            )
    )
)

[tyezza]

Success!  Thank you Eric!  Appreciate everyones time.

[Ashish_Mathur]

Hi,

I am not clear with your requirement.  What is the purpose of Table1?  Also for Table2, use a relative period date slicer toshow you today's date only.  Drag Case ID to the visual.   Won't that serve your purpose.

[dilumd]

Hi,

I'm not sure whether I understood your outcome properly, but below formula can segregate cases which are "due today" with "open" status and "follow up" type.

Open Case Due Today = CALCULATE(COUNTROWS(Requirement),FILTER(Requirement,Requirement[Due Date]=TODAY()),FILTER(Requirement,Requirement[Type]="Follow up"),FILTER(Requirement,Requirement[Status]="Open"))