Forum Discussion

j_w's avatar
j_w
Icon for Helper IV rankHelper IV
6 years ago
Solved

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

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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.

    • j_w's avatar
      j_w
      Icon for Helper IV rankHelper IV
      6 years ago

      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.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        Hi j_w ,

         

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

         

        Glad it worked.