Problems with Filter on Calculated Table

[Txtcher]

I am trying to create a table visual that displays records based on the "as-of-date." 

I have a measure I have been using to calculate the count of records on the "as-of-date" and I think it is working. I use this measure to create a chart. But, now I want the records (included in the count) to display below the chart that will dynamically change based on the date slicer.

Here is the measure:

Backlog = 
 VAR vSvyDate = LOOKUPVALUE('RS Events'[Survey Date],'RS Events'[Id],SELECTEDVALUE('RS Cases'[RS Event ID])) 
 RETURN
 CALCULATE(
    COUNTROWS('RS Cases'),
    SUMMARIZE('RS Cases','RS Cases'[Program]),
    FILTER(
        ALL('RS Cases'),
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="OPEN") ||
        // past due cases that are closed but the survey date is greater than the calendar date
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="CLOSED" &&
        vSvyDate> Max('Calendar'[Date])) ||
        // cases past due and survey date is blank and status is closed and the status change date is greater than today's date
        'RS Cases'[Due By]<=Max('Calendar'[Date]) && ISBLANK(vSvyDate) &&
        'RS Cases'[Status]="CLOSED" && 'RS Cases'[Status Change Date]>max('Calendar'[Date]))

When I adapted this code to create a new table in the model:

Backlog Cases = 
 VAR vSvyDate = LOOKUPVALUE('RS Events'[Survey Date],'RS Events'[Id],SELECTEDVALUE('RS Cases'[RS Event ID])) 
 RETURN
     FILTER(
        ALL('RS Cases'),
        // Due by less than as of date and case status is open
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="OPEN") 
        ||
        // Due by less than as of date (calendar date), status closed but the survey date is greater than the calendar date
        ('RS Cases'[Due By]<=Max('Calendar'[Date]) && 'RS Cases'[Status]="CLOSED" &&
        vSvyDate> Max('Calendar'[Date]))
         ||
        // cases past due and survey date is blank and status is closed and the status change date is greater than today's date
        'RS Cases'[Due By]<=Max('Calendar'[Date]) && ISBLANK(vSvyDate) &&
        'RS Cases'[Status]="CLOSED" && 'RS Cases'[Status Change Date]>max('Calendar'[Date]))

I then created a relationship with this table to my calendar table with the Sent to Region field (date field):

But when I look at the results, it appears the last 2 OR statements in the Filter are not producing any records and they should be.

Is it the order of the filter statements? 
And, if anyone has any suggestions on how to make this easier to read, please do not hesitate.

Also, I need to handle the null values in the Due By field - I want to exclude them, but haven't added that to the measure or the make table until I can figure out why they are not working.

Thanks in advance.

[v-ssriganesh]

Hello @Txtcher,

I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[v-ssriganesh]

Hi @Txtcher,
Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information or anything unrelated to the issue or question. Also, show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Txtcher]

Hello, @v-ssriganesh 

When I clicked the link for help uploading data, this is the message I received:

I will try to find time today to see if I can locate the instructions regarding how to provide data.  Thank you for the follow-up because I still have not been able to resolve this issue.

[v-ssriganesh · Accepted solution]

Hello @Txtcher,
Sorry for the inconvenience caused. Please refer the following link to provide sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[v-ssriganesh]

Hello @Txtcher,
Just checking in to see if you had a chance to review my earlier message and share the requested details. Once we have that, we’ll be better equipped to help you further. Thanks.

[Bibiano_Geraldo]

Hi @Txtcher ,

When you tried to build a separate “Backlog Cases” table in DAX, it never updated when you moved the date slicer because calculated tables are only evaluated at data‐refresh time so expressions like MAX(Calendar[Date]) inside that table always refer to the single largest calendar date in the model (not the slicer selection) and SELECTEDVALUE inside a calculated table does not provide a row‐by‐row context. As a result, your last two OR clauses never matched any rows. Instead of a static table, you should turn your backlog logic into a measure (often called a “flag” measure) that returns 1 when a case meets the open-past-due or closed-but-still-active conditions as of MAX(Calendar[Date]) (which does respond to the slicer). Then, place a normal Table visual on the report, add the RS Cases columns you want to see, and use the flag measure as a visual-level filter (is 1) so the table dynamically shows only those rows for whatever “as-of” date the user picks.

BacklogFlag =
VAR vASOF =
    MAX( 'Calendar'[Date] )
// Find the Survey Date for this Case’s related Event.
// (We assume [RS Event ID] is a valid FK into 'RS Events'[Id].)
VAR vSvyDate =
    LOOKUPVALUE(
        'RS Events'[Survey Date],
        'RS Events'[Id],
        'RS Cases'[RS Event ID]
    )
// Exclude rows where Due By is blank:
VAR HasDueBy =
    NOT( ISBLANK( 'RS Cases'[Due By] ) )
// Condition 1: case is due on or before as-of, and still OPEN
VAR IsOpenPastDue =
    HasDueBy
    && 'RS Cases'[Due By] <= vASOF
    && 'RS Cases'[Status] = "OPEN"
// Condition 2: case DueBy ≤ as-of, status=Closed, but survey date comes after as-of
VAR IsClosedStillInSurveyWindow =
    HasDueBy
    && 'RS Cases'[Due By] <= vASOF
    && 'RS Cases'[Status] = "CLOSED"
    && NOT( ISBLANK( vSvyDate ) )
    && vSvyDate > vASOF
// Condition 3: case DueBy ≤ as-of, status=Closed, but no survey date at all,
//            and “Status Change Date” is after as-of
VAR IsClosedNoSurveyYet =
    HasDueBy
    && 'RS Cases'[Due By] <= vASOF
    && 'RS Cases'[Status] = "CLOSED"
    && ISBLANK( vSvyDate )
    && 'RS Cases'[Status Change Date] > vASOF
RETURN
    IF(
        IsOpenPastDue
        || IsClosedStillInSurveyWindow
        || IsClosedNoSurveyYet,
        1,
        0
    )

[Txtcher]

Hi @Bibiano_Geraldo 

When I get to the Lookup function, the intellisense will not let me select an RS Case field. I have to manually type it in which tells me something is wrong. Here is what I have for the backlog flag measure based on your response(I changed some variable names but that was all)

BacklogFlag1 = 
VAR vAsOfDate = 
    MAX('Calendar'[Date])
// Find the survey date for this intake
VAR vSurveyDate = 
    LOOKUPVALUE(
        'RS Events'[Survey Date],
        'RS Events'[Id],
        'RS Cases'[RS Event ID]
    )
//Exclude blank due by dates
VAR vHasDueDate = 
    Not(ISBLANK('RS Cases'[Due By]))
//Condition 1: Due Date before as of date and status is Open
VAR vOpenPastDue = 
    vHasDueDate
    && 'RS Cases'[Due By] <= vAsOfDate
    && 'RS Cases' [Status] = "OPEN"
// Condition 2: Due Date before as of date, Status is closed, but Survey Date after due date and before as of date
VAR vClosedStillInSurveyWindow = 
    vHasDueDate
    && 'RS Cases'[Due By] <=vAsOfDate
    && 'RS Cases'[Status] = "CLOSED"
    && NOT(ISBLANK(vSurveyDate)
    && vSurveyDate <=vAsOfDate
// Condition 3: Due date < as of date, Status closed, No Survey, Status Change date < as of date
VAR vClosedWithoutSurvey =
    vHasDueDate
    && 'RS Cases'[Due By] <=vAsOfDate
    && 'RS Cases'[Status] = "CLOSED"
    && ISBLANK(vSurveyDate)
    && 'RS Cases'[Status Change Date] > vAsOfDate
RETURN
    IF(
        vOpenPastDue,
        || vClosedStillInSurveyWindow,
        || vClosedWithoutSurvey,
        1,
        0
    )

Here are the gazillion errors 😥

In the model, the RS Events table is linked to the RS Cases table via the RS Events[Id] field and the RS Cases[RS Event ID]

Can you tell what is going on here?

[bhanu_gautam]

@Txtcher , Try using

DAX
Backlog =
VAR vSvyDate = LOOKUPVALUE('RS Events'[Survey Date], 'RS Events'[Id], SELECTEDVALUE('RS Cases'[RS Event ID]))
RETURN
CALCULATE(
COUNTROWS('RS Cases'),
FILTER(
ALL('RS Cases'),
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "OPEN") ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "CLOSED" && vSvyDate > MAX('Calendar'[Date])) ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && ISBLANK(vSvyDate) && 'RS Cases'[Status] = "CLOSED" && 'RS Cases'[Status Change Date] > MAX('Calendar'[Date]))
)
)

DAX
Backlog Cases =
VAR vSvyDate = LOOKUPVALUE('RS Events'[Survey Date], 'RS Events'[Id], SELECTEDVALUE('RS Cases'[RS Event ID]))
RETURN
FILTER(
ALL('RS Cases'),
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "OPEN" && NOT ISBLANK('RS Cases'[Due By])) ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && 'RS Cases'[Status] = "CLOSED" && vSvyDate > MAX('Calendar'[Date]) && NOT ISBLANK('RS Cases'[Due By])) ||
('RS Cases'[Due By] <= MAX('Calendar'[Date]) && ISBLANK(vSvyDate) && 'RS Cases'[Status] = "CLOSED" && 'RS Cases'[Status Change Date] > MAX('Calendar'[Date]) && NOT ISBLANK('RS Cases'[Due By]))
)