Power BI Challenge 6 - Insurance Complaints

Challenges Insurance Complaints
83

Thank you for your feedback @MudassirAli, Of course I will share the DAX formula. I struggled to figure that out too to be honest, but I found a YouTube video on Curbal that explains how to calculate similar situations and shout out to @Greg he posted similar DAX formulas in the forum as well.

Here is the link to the video :-

So, my understanding is there is a status id ( 1 - 9 ) column grouped by complain Id and date of the status update on the Status History table. Therefore, what I did was I created a calculated column that captures the day difference from previous row date and current row date for each complain Id Group and stamps it in a column.

The logic I used is :-

Diff. Current v Prev.s Dates = 
VAR Mindate = CALCULATE( MIN( Complains[Complain Date] ) , ALLEXCEPT( Complains , Complains[ID] ) )
VAR Demoteddate = 
            CALCULATE( 
                MAX( 'Status History'[Status Date] ) , 
                FILTER( 
                    ALLEXCEPT( 'Status History' , 'Status History'[Complaint ID] ) ,
                    'Status History'[Status Date] < EARLIER( 'Status History'[Status Date] ) ) )
RETURN
IF( 'Status History'[Status Date] = Mindate , 
        DATEDIFF( 'Status History'[Status Date] , 'Status History'[Status Date] , DAY ) ,
              DATEDIFF( Demoteddate , 'Status History'[Status Date] , DAY ) )

I used the Complains Fact table complain date as a starting date of the claim.

Then I used that calculated column to create following measure to concatenate the day numbers to " Days".

Status Progression by Day = 

SWITCH( TRUE() ,
    CALCULATE( SUMX( (  'Status History' )  , 'Status History'[Diff. Current v Prev.s Dates] ), 
        USERELATIONSHIP( 'Status History'[Complaint Status ID] , Statuses[ID] ) ) = BLANK()  , BLANK() ,
            CONCATENATE( CALCULATE( SUMX( (  'Status History' )  , 'Status History'[Diff. Current v Prev.s 
                                           Dates] ), 
                                   USERELATIONSHIP( 'Status History'[Complaint Status ID] , Statuses[ID] ) ) , " Days"  
                    )
                    
                     )

Hope that was clear.

Regards,
Nebiyu

3 Likes