Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Any direction would be very much appreciated.
Thanks all.
Solved! Go to 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.
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] ) ) )
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 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.
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.
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.
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"))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |