Distinct count filtered by Greater than & Less than, and by other column value.

[Flawn]

Hello All

I'm trying to perform what I thought would be a relatively simple calculation, but I am unable to get it to work. 

I had set up a conditional formating coloration on a visual table and thought I could essentially reproduce its effects for a bar graph as a partner visual. The conditional works fine - this is it for reference:

And here's the simple measure the conditional formating is being based on:

Active Session Count =

CALCULATE(

    DISTINCTCOUNT('mcs_clientinsession'[Session]),

    'mcs_clientinsession'[mcs_clientincase(mcs_clientincase).statuscodename]

        IN { "Active" }

)

Pretty simple! When the Distinct Count of Sessions with the "Active" status are between certain values, they need to be identified and highlighted.

But when I tried to reproduce it in DAX for the adjoining var graph for the counts, nothing I produced seemed remotely correct - generally producing errors.

The data structure is pretty simple (the status will always be the same across an entire CaseID) - as an example:

Case ID	Session ID	Status
A	A1	Active
A	A2	Active
A	A3	Active
B	B1	Inactive
B	B2	Inactive
C	C1	Inactive
C	C2	Inactive
C	C3	Inactive
C	C4	Inactive

I need to be able to produce a bar graph that shows the number of ACTIVE Distinct CaseIDs with 15-17 distinct sessions, 18-19 distinct sessions, and 20+distinct sessions.

Thanks again for any help you can provide.

Warm Regards,
Flawn

[bhanu_gautam]

@Flawn , you can create a measure that counts the distinct Case IDs with the specified number of distinct sessions.

DAX
-- Measure to count distinct sessions for each Case ID with "Active" status
ActiveSessionCountPerCase =
CALCULATE(
DISTINCTCOUNT('mcs_clientinsession'[Session ID]),
'mcs_clientinsession'[Status] = "Active"
)

-- Measure to categorize the counts into specified ranges
ActiveCaseCountInRange =
VAR ActiveCases =
ADDCOLUMNS(
SUMMARIZE(
'mcs_clientinsession',
'mcs_clientinsession'[Case ID]
),
"ActiveSessionCount", [ActiveSessionCountPerCase]
)
RETURN
SUMX(
ActiveCases,
SWITCH(
TRUE(),
[ActiveSessionCount] >= 15 && [ActiveSessionCount] <= 17, 1,
[ActiveSessionCount] >= 18 && [ActiveSessionCount] <= 19, 1,
[ActiveSessionCount] >= 20, 1,
0
)
)

-- Measure to count the number of cases in each range
CountOfCasesInRange =
SUMX(
VALUES('mcs_clientinsession'[Case ID]),
SWITCH(
TRUE(),
[ActiveSessionCountPerCase] >= 15 && [ActiveSessionCountPerCase] <= 17, 1,
[ActiveSessionCountPerCase] >= 18 && [ActiveSessionCountPerCase] <= 19, 1,
[ActiveSessionCountPerCase] >= 20, 1,
0
)
)

You can then use the CountOfCasesInRange measure to create your bar graph. This measure will count the number of distinct Case IDs that fall into each of the specified session count ranges.

[Flawn]

Hi Bhanu,

Firstly, thank you! This seems like a good start - but it isn't quite there yet. When I use the formula as described it produces the folowing bar graph.

Which is the correct count, but every category has been put together into a single bar. I need to split it up into each grouping - one bar for 15-17, one bar for 18-19, and one bar for 20+. How would i go about seperating the  ranges on the bar chart as described?

[v-yaningy-msft]

Hi, @Flawn 

Thanks for bhanu_gautam reply. If you want to achieve classification of all data, the X-axis must be filled with data similar to Calculate Colum similar to what you can try to classify the original data. Or you can provide a pbix file with no sensitive data for testing.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum