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
j_w
Helper IV
Helper IV

DAX how to count conditionally

image.png

Notes:

1. The TestPeriod field is the number type and increases for the next new trial period

Question:

How to write DAX to calculate the unique TestUser number that never happened in previous tests (not including the current selected test period) during a selected test period?

For example:

For TestPeriod 2003, all 4 test users are counted, because there is no pre-test

For TestPeriod 2004, 3 users C, D, E are counted:

- User A has a record of apaso in the previous TestPeriod 2003, not counted

- User B has a record of overdoing in the previous TestPeriod 2003, not counted

- User C, D, E has no pass record in previous test periods, counted (note: user D joined two tests in the same 2004 trial period, but must be counted as a user in the DAX)

For TestPeriod 2005, 2 D and F users are counted

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @j_w ,

 

Try the following measure:

Measure =
VAR Current_Period =
    SELECTEDVALUE ( Tests[TestPeriod] )
VAR temp_table_previous =
    SUMMARIZE (
        FILTER (
            ALL ( Tests[TestPeriod]; Tests[TestResult]; Tests[TestUser] );
            Tests[TestPeriod] < Current_Period
                && Tests[TestResult] = "PASS"
        );
        Tests[TestUser]
    )
VAR temp_table_current =
    SUMMARIZE (
        FILTER ( ALL ( Tests ); Tests[TestPeriod] = Current_Period );
        Tests[TestUser]
    )
VAR count_new_user =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Tests; Tests[TestUser]; Tests[TestPeriod] );
            "TTT"; CALCULATE ( COUNT ( Tests[TestUser] ); Tests[TestPeriod] < Current_Period )
        );
        [TTT] = BLANK ()
    )
RETURN
    COUNTROWS ( count_new_user )
        + COUNTROWS ( INTERSECT ( temp_table_previous; temp_table_current ) )

 

This is giving me the correct result however with such a small number of datapoints there can be an error.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @j_w ,

 

Try the following measure:

Measure =
VAR Current_Period =
    SELECTEDVALUE ( Tests[TestPeriod] )
VAR temp_table_previous =
    SUMMARIZE (
        FILTER (
            ALL ( Tests[TestPeriod]; Tests[TestResult]; Tests[TestUser] );
            Tests[TestPeriod] < Current_Period
                && Tests[TestResult] = "PASS"
        );
        Tests[TestUser]
    )
VAR temp_table_current =
    SUMMARIZE (
        FILTER ( ALL ( Tests ); Tests[TestPeriod] = Current_Period );
        Tests[TestUser]
    )
VAR count_new_user =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( Tests; Tests[TestUser]; Tests[TestPeriod] );
            "TTT"; CALCULATE ( COUNT ( Tests[TestUser] ); Tests[TestPeriod] < Current_Period )
        );
        [TTT] = BLANK ()
    )
RETURN
    COUNTROWS ( count_new_user )
        + COUNTROWS ( INTERSECT ( temp_table_previous; temp_table_current ) )

 

This is giving me the correct result however with such a small number of datapoints there can be an error.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

 

Thanks for your reply, it works great, I have to replace all the semicolon to comma, otherwise there was DAX syntax error for me.

Hi @j_w ,

 

That has to do with regional settings in my computer I use the ; as syntax separator.

 

Glad it worked.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.