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

 

DataData

Any direction would be very much appreciated.

 

Thanks all.

1 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]
            )
    )
)

View solution in original post

7 REPLIES 7
tyezza
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.
dilumd
Solution Supplier
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.

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


@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]
            )
    )
)

Success!  Thank you Eric!  Appreciate everyones time.

Ashish_Mathur
Super User
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/
dilumd
Solution Supplier
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"))

Power BI Help.JPG

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.