cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

The expected distinct counts should be 2 DvUsers for Completed 1 for Not Started and 1 for In Progress

2 REPLIES 2
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?

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors