Forum Discussion

Txtcher's avatar
Txtcher
Helper V
1 year ago
Solved

Problems with Filter on Calculated Table

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 work...
Need Help
Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
Txtcher
Helper V
1 year ago

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?