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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee

@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]
)
)
)
```
7 REPLIES 7
Frequent Visitor
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.
Solution Supplier

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

@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]
)
)
)
```
Frequent Visitor

Success!  Thank you Eric!  Appreciate everyones time.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Solution Supplier

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors