cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated Measure that filters across 2 tables using an inactive relationship

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

@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
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.

@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[Status] = "open"
&& Requirements[DueDate] = TODAY()
),
Requirements[Caseid], 'Case'[CaseId]
)
)
)
```
7 REPLIES 7
Established Member

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

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"))`

Super User

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

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.

Frequent Visitor

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

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.
Established Member

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

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.

Frequent Visitor

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

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
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.

Moderator

## Re: Calculated Measure that filters across 2 tables using an inactive relationship

@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
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.

@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,