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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Distinct count of application ids based on application status

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 StatusApplication IDDistinct count of Application ID
New10
New21
New30
New41
New50
Completed11
On hold31
Suspended51
  5
1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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.

App.PNG

 

 
lbendlin
Super User
Super User

How is the data sorted when it comes in? Do you have timestamps for status changes?

Anonymous
Not applicable

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.