Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |