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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
darithear
Frequent Visitor

The count distinct of users based on an overall status calculation display incorrectly in a visual

Relative PBI noob here.  I am having issues writing a dax calculation that counts the users correctly based on an overall status when a field is filtered to more than one value.  The expectation here is that if Name is filtered for one or more value, an overall % complete and overall status is determined based off the filter.

 

This is the sample data:

DvUser Name DvState
Aamir S Orientation Assigned
Aamir S Simulation Enrolled
Aaron H Orientation In Progress
Aaron H Simulation Enrolled
Aaron L Orientation Completed
Aaron L Simulation Completed
Aaron S Orientation Completed
Aaron S Quick Start Assigned
Aaron S Simulation Completed

I have a measure that calculates Overall Percentage Complete based on the DvState as follows:

Overall Percentage Complete =
VAR WeightedCounts =
    SUMMARIZE(
        FILTER('sample frED data forPBI', 'sample frED data forPBI'[Name] = SELECTEDVALUE('sample frED data forPBI'[Name])),
        'sample frED data forPBI'[DvState],
        "WeightedCount",
        SWITCH(
            TRUE(),
            'sample frED data forPBI'[DvState] = "Completed", 1,
            'sample frED data forPBI'[DvState]= "In Progress", 0.1,
            TRUE(), 0
        )
    )
VAR TotalWeightedCount =
    SUMX(WeightedCounts, [WeightedCount])
RETURN
    DIVIDE(
        TotalWeightedCount,
        COUNTROWS(VALUES('sample frED data forPBI'[Name])),
        0
    )
Then Overall status column based on the Overall Percentage Complete:
Overall Status = IF([Overall Percentage Complete] = 1,"Completed",IF([Overall Percentage Complete] = 0, "Not Started","In Progress"))
 
The expected results should be if Orientation and Simulation is filtered under Name, the overall status for the following DvUsers are Aamir S = Not Started, Aaron H = In Progress, Aaron L = Completed and Aaron S = Completed.  But when I go create a visual with Overall Status as the legend and Count Distinct DvUser as the values I get the wrong counts.
 
Overall.Counts.by.Status.PNG
The expected distinct counts should be 2 DvUsers for Completed 1 for Not Started and 1 for In Progress
 
A DvUser looks to have been counted twice.  Please advise.
2 REPLIES 2
darithear
Frequent Visitor

Thanks for the support.  How do I ensure I have the correct visual setup in Power BI to display the results as expected?  I am still unable to drag Overall Status to the legend?

123abc
Community Champion
Community Champion

It appears that you are trying to calculate an overall status and count distinct DvUsers based on the filter selections in your Power BI report, and you are encountering an issue where some DvUsers are being counted multiple times. To achieve the desired results, you need to adjust your DAX measures.

Here's a revised approach to calculate the distinct counts of DvUsers based on the filtered Name values and their corresponding overall status:

  1. Create a measure to calculate the Overall Percentage Complete:

Overall Percentage Complete =
VAR FilteredNames = VALUES('sample frED data forPBI'[Name])
RETURN
DIVIDE(
SUMX(FILTER('sample frED data forPBI', 'sample frED data forPBI'[Name] IN FilteredNames),
SWITCH(
'sample frED data forPBI'[DvState],
"Completed", 1,
"In Progress", 0.1,
0
)),
COUNTROWS(FilteredNames),
0
)

 

In this measure, we calculate the Overall Percentage Complete by filtering the data based on the selected Name values and then summing the weighted counts.

  1. Create a measure for Overall Status:

Overall Status = VAR OverallPctComplete = [Overall Percentage Complete] RETURN IF( OverallPctComplete = 1, "Completed", OverallPctComplete = 0, "Not Started", "In Progress" )

 

This measure calculates the Overall Status based on the Overall Percentage Complete measure you've already defined.

  1. Create a visual with "Name" as the axis (or another suitable dimension) and use "Overall Status" as the legend. Then, use the "Count Distinct DvUser" measure to count the distinct DvUsers.

With this approach, the distinct counts of DvUsers for each Overall Status should be calculated correctly based on the filtered Name values. Ensure that you have the correct visual setup in Power BI to display the results as expected.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors