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

[darithear]

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

A DvUser looks to have been counted twice.  Please advise.

[darithear]

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]

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.