Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX power query

Hi Super Users,

I am trying to count the number of case ID's that have SN23 procedure code which went through SN21 or MR28 Procedure Code before.  the data is as follows

Case IDProcedure codeDate
1505SN21May-19
1505SN2110/5/2019
1505SN232/6/2020
1508SN214/7/2017
1508MR285/10/2014
1509SN2110/10/2019
1509SN2310/23/2019
1511SN232/5/2020
1512SN238/9/2020
1509MR282/6/2020
1513MR2812/5/2020
1513SN231/5/2021
3 REPLIES 3
daxer-almighty
Solution Sage
Solution Sage

 

Count = 
// Date must be a real date, not a string.
var MinDate = MIN( T[Date] )
var CodeOfInterest = "SN23"
var CodesFromBefore = {"SN21", "MR28"}
var CasesOfInterest =
    FILTER(
        GROUPBY(
            CALCULATETABLE(
                // This SUMMARIZE finds the pairs of Case ID
                // and Procedure Code in T where dates are
                // before the min date visible and codes
                // are filtered to be only the ones we're
                // interested in.
                SUMMARIZE(
                    T,
                    T[Case ID],
                    T[Procedure Code]
                ),
                T[Date] < MinDate,
                T[Procedure Code] in CodesFromBefore,
                // This CALCULATETABLE returns only
                // the Case ID's we're interested in
                // from the current context.
                CALCULATETABLE(
                    DISTINCT( T[Case ID] ),
                    KEEPFILTERS( 
                        T[Procedure Code] = CodeOfInterest 
                    )
                )
            ),
            // The GROUPBY takes the table from above and
            // checks for each ID how many codes it
            // has. If the number is 2, then we've got
            // the CaseID we've been after. If it's one
            // then one code is missing.
            T[Case ID],
            "@CodeCount", SUMX( CURRENTGROUP(), 1 )
        ),
        // So now we are filtering out the case id's
        // that have just one code.
        [@CodeCount] = 2
    )
var Result = COUNTROWS( CasesOfInterest )
RETURN
    Result

 

 

 

Anonymous
Not applicable

syntax was correct but it was not showing up the count. Instead resulting in BLANK

Tanushree_Kapse
Impactful Individual
Impactful Individual

Hi @Anonymous ,

Use the below measure:
CountIds=
IF(AND(SELECTEDVALUE(Procedure code)= "SN23",OR(SELECTEDVALUE(Procedure code)= "SN21",SELECTEDVALUE(Procedure code)= "MR28")) ,CALCULATE(COUNT(Case Id)))

 

I hope this helps!
Kudos are always appreciated!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors