Need help using DAX on Slow Changing Dimension Type 2 Table

DAX DAX Calculations
1

Hi all! I’ve been banging my head against a wall for about a week now trying myriad ways of solving this challenge, and after countless hours of reading and watching videos, I’m turning to the community hoping someone here can guide me in the right direction.

Input:
Two tables that tracks cases, each from a different vendor, and is updated every workday. Each case has an upload date, an ID, and a value, among other fields. TABLE A has the most recent upload date as the current workday, and TABLE B has the most recent upload date as the previous work day. Each time the table is updated, new rows are added, and the existing rows remain. Sometimes the new rows are new cases, sometimes new rows have the same ID with updated values, and sometimes the new rows are exactly the same case from the previous update. From my research, this table is closely related to a slow changing dimension type 2.
In PQ, I appended these two tables together as the ‘All Data’ table.
There’s also a third TABLE C explained below

Use Case:
Each case must be put into one of three buckets, Active, Cancelled, or Approved.
Active bucket = case has the most recent upload date
Cancelled bucket = case dropped off the most recent upload date
Approved bucket = a third TABLE C, with list of approved case IDs

The Problem:
User wants to dynamically filter the ‘All Data’ table so that it shows all the cases ‘as of’ a date. For example, if user slices by Last Month, the max and min date will be 2/28/23 and 2/1/23, respectively, meaning the most recent upload date on the All Data table should be 2/28/23, and all cases would fall in there respective buckets as of this date.

Restrictions:
I do not have access to the SQL table. And I cannot use direct query.

SCDsample.pbix (92.4 KB)
SCDsample.xlsx (27.8 KB)

Any help would be greatly appreciated!

2

Hi @Schreg
Here is a sample solution based on how I understand the problem description. See attached images for a quick verification.

image
image1371×782 76 KB

DAX Explanation:
Each of the case buckets are written as variables.

  • Approved: If a case exists in the Approved Cases table (TABLE C). I also added a different of version of the variable which checks that a case has dropped off and is in the Approved Cases table then classifies as “Approved”.
  • Cancelled: If a case’s max upload date (given the user’s selection) is beyond the max date the user selected.
  • Active: If a case’s max upload date (given the user’s selection) is equal to the max date the user selected.
    Note: In the SWITCH statement, the order of the switch statement does matter. 
Case Bucket = 
VAR MaxSelectedDate = MAX( 'Date Table'[Date] ) -- Retreives Date to compare with
VAR ApprovedCases = ALL( TABLE_C[Case ID], TABLE_C[Vendor] ) -- Table of Approved cased and vendor
VAR CurrentDate = -- Calculates the Last Upload date for a case considering the selected date period
    CALCULATE( 
        MAX( 'All Data'[Upload Date] ), 
        ALLEXCEPT( 'All Data', 'All Data'[Case ID], 'All Data'[Vendor], 'Date Table' ) 
    )
VAR CurrentCase = 
    SUMMARIZE( 'All Data', 'All Data'[Case ID], 'All Data'[Vendor] )  -- Current Case ID and Vendor in the filter context
VAR IsActive = CurrentDate = MaxSelectedDate -- The case last upload date equals the max selected date
VAR IsApproved = 
    NOT( ISEMPTY( INTERSECT( ApprovedCases, CurrentCase ) ) ) -- Checks if current case is in the approved case
/*
Different interpretation for Approved
Checks if the Case ID's upload date has dropped off AND the case is in the approved table
VAR IsApproved = 
    ( CurrentDate < MaxSelectedDate ) && NOT( ISEMPTY( INTERSECT( ApprovedCases, CurrentCase ) ) )

*/
VAR IsCancelled = CurrentDate < MaxSelectedDate -- Checks if the Case's upload date has dropped off
RETURN
    SWITCH( 
        TRUE(),
        IsApproved, "Approved", 
        IsActive, "Active",
        IsCancelled, "Cancelled"
    )

SCDsample.pbix (97.4 KB)

3

Hi @BolajiO , thank you so much for your quick response!

I will digest your description today, but when I opened the pbix file this error is popped up:

Untitled
Untitled1882×840 92.5 KB

Could it be something with my settings? or PBI version?

4

I don’t think it’s an issue with Power BI version.

Check that the Case ID column and Vendor column in the ‘All Data’ table and TABLE_C are the same data type. Case ID as Integer, Vendor as Text.

5

Hi There,

FYI: I just openned the file with no issues and i have the most current version of Power bi (feb 2023)

Thanks
Keith

1 Like
6

thanks for the heads up @Keith

1 Like
7

Hello @Schreg

Good to see that you are having progress with your inquiry. Did the response from @BolajiO help you solve your inquiry?

If it does, kindly mark his answer as the SOLUTION to your inquiry.

If not, how far did you get and what kind of help do you need further?