Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Good day. I need help on creating DAX formula for the application example.
Background information:
The application starts off being 'new' and depending on the situation, will progress on to become either 'Completed' , 'On-hold' or 'Suspended'. We will count the application id only once. For example as shown in table below, application ID 1 progresses from 'New' to 'Completed' ,and therefore is counted as 'Completed' once. Application id 5 progresses from 'New' to 'Suspended' and therfore is counted as 'Suspended' once. If the application is new and have no updated progress i.e. application ID 2 and 4, it will be counted 'New' once.
My distinct count calculation would be 5, broken by according to the application status. That is, 2 new applications, 1 completed, 1 suspended and 1 On-hold.
Any help would be appreciated. Thanks!
Application Status | Application ID | Distinct count of Application ID |
New | 1 | 0 |
New | 2 | 1 |
New | 3 | 0 |
New | 4 | 1 |
New | 5 | 0 |
Completed | 1 | 1 |
On hold | 3 | 1 |
Suspended | 5 | 1 |
5 |
Solved! Go to Solution.
This expression gives your expected results from the example data
DC App ID =
SUMX (
'ID',
CALCULATE (
VAR __notNew =
SELECTEDVALUE ( 'ID'[Application Status] )
IN { "Completed", "On Hold", "Suspended" }
VAR __onlyNew =
CALCULATE (
DISTINCTCOUNT ( 'ID'[Application Status] ),
ALLEXCEPT ( 'ID', 'ID'[Application ID] )
) = 1
RETURN
IF ( OR ( __notNew, __onlyNew ), 1, 0 )
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This expression gives your expected results from the example data
DC App ID =
SUMX (
'ID',
CALCULATE (
VAR __notNew =
SELECTEDVALUE ( 'ID'[Application Status] )
IN { "Completed", "On Hold", "Suspended" }
VAR __onlyNew =
CALCULATE (
DISTINCTCOUNT ( 'ID'[Application Status] ),
ALLEXCEPT ( 'ID', 'ID'[Application ID] )
) = 1
RETURN
IF ( OR ( __notNew, __onlyNew ), 1, 0 )
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat.
I used the DAX measure and I can’t apply Application ID field as shown below. Can you run me through your thought process as well please?
Thanks.
How is the data sorted when it comes in? Do you have timestamps for status changes?
The only time stamp provided is the day. For example on 15 May 2020, the application status changes from 'New' to 'Completed'. Or it could be the it takes three days to change its status from 'New' to 'Suspended'. the application will always start off as 'New' and will take on the new status accordingly.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |