Create Dynamic Measure of Distinct Counts

[SoupyNoodles14]

Hello! I know this theme is coming up a lot in this forum and I apologize I have been struggling for weeks on this. My data is structured as follows.

I built the following table to show a pairing of each customer visit to a store AND their prior service history. For instance, at visit 1, person A has two prior services as of 1/1/2021, but at their second visit in 1/1/2023, they have four prior services done.

(Note, dates are in MM/DD/YY format.) 

Person	VisitID	VisitDate	PriorServiceID	PriorServiceType	PriorServiceDate
A	V1	1/1/21	S1	High	1/1/03
A	V1	1/1/21	S2	Medium	1/2/03
A	V2	1/1/23	S1	High	1/1/03
A	V2	1/1/23	S2	Medium	1/2/03
A	V2	1/1/23	S3	Low	1/3/22
A	V2	1/1/23	S4	Low	7/3/22
B	V3	1/1/23	NULL	NULL	NULL
C	V4	1/3/23	S5	Low	1/18/22
C	V5	1/3/24	S5	Medium	1/18/22
C	V5	1/3/24	S6	High	7/17/23

I have loaded this data in power BI, where this table above (say, Priors) is linked to a separate visitID-level table (Visits). There are slicers on visit date, prior service type, and prior service date. 

I want to use Measures in Power BI to obtain the following output using measures (NOT calculated columns). 

(V3 is with 0 prior services, so the first entry in the output below is 0. V4 is associated with 1 prior service. V1 and V5 are both associated with 2 prior services, so that makes the third entry. The final entry is for V2.)

DESIRED OUTPUT USING MEASURES:

# Prior Services	# Unique VisitIDs
0	1
1	1
2	2
4	1

I want to be able to slice this so that if I were to select only prior services performed in 2003 on a slicer, I will obtain:

# Prior Services	# Unique VisitIDs
2	2

Similarly, I want to be able to slice by the prior service type and the most severe prior service type associated with each visit. 

So far, I can achieve the desired output WITHOUT slicers easily by building a calculated table uses the SUMMARIZE function. Naturally calculated columns cannot be re-calculated after I select a slicer which is of crucial importance. I have used many combinations of ALLEXCEPT, ALL, COUNTX, COUNTDISTINCTNOBLANK (...) + 0, and FILTER. I know that one of them has to be successful, but at this point I'd appreciate any assistance you can offer. Thank you in advance. 

[Rupak_bi · Accepted solution]

Hi, Hope this will solve your purpose

Approach I have used: 

1. Created an axis table with nubler 1,2,3,4 using manual entry.
2. created Measure to switch values to the axis as below

Measure =

Var Tab = SUMMARIZE('Table','Table'[VisitID],"Count",max(0,countx(filter('Table','Table'[PriorServiceID]<>"NULL"),'Table'[PriorServiceID])))

return

countx(FILTER(Tab,[Count]=max('Table (2)'[axis])),[Count])

This is working with slicing as well. If this works, accept as solution else let me know the issue.

Thanks..

[SoupyNoodles14]

Hi @Rupak_bi  and all. Sorry to keep bugging you, I have a final follow-up question on this. I have successfully created measures and charts to indicate counts of visits for both greatest prior service severity AND for # prior services. I have lookup tables for both the prior service severity and for the # of prior services which now serve as slicers in the report (this is essentially Table (2) in Rupak's post). These slicers are being sliced by my other slicers but they alone do not slice anything. Is there any way to use them to slice other data in the report? For instance, if we can use the "greatest severity of prior service" to slice on the prior service date. Thank you in advance. 

[Rupak_bi · Accepted solution]

Hi, Hope this will solve your purpose

Approach I have used: 

1. Created an axis table with nubler 1,2,3,4 using manual entry.
2. created Measure to switch values to the axis as below

Measure =

Var Tab = SUMMARIZE('Table','Table'[VisitID],"Count",max(0,countx(filter('Table','Table'[PriorServiceID]<>"NULL"),'Table'[PriorServiceID])))

return

countx(FILTER(Tab,[Count]=max('Table (2)'[axis])),[Count])

This is working with slicing as well. If this works, accept as solution else let me know the issue.

Thanks..

[SoupyNoodles14]

Thank you Rupak! This works PERFECTLY and is a huge relief toward helping organize a very large dataset. I have one follow-up question. 

The last part of my post above asks about slicing the most severe type of prior service. That would mean the measure I'm thinking of would identify # of visits by "at least one high-level prior service", "at least one medium-level prior service", "at least one low-level prior service", and "no prior services", where the counts would change based on other selections in the report. Just like you created the axis table above, I'm thinking about listing these string values out and then calculating the relevant counts in a measure. Is there any way to accomplish this? 

[Rupak_bi]

You can do it same way as previous one. Let me know if you stuck.

[SoupyNoodles14]

Hi Rupak - I am achieving partially correct results. It is correct for "no prior services" but it is not correctly identifying the severity for the others. 

Here is what my approach is. 

1. I created a separate lookup table with each severity and I loaded it into my dataset. 

SeverityLiteral	SeverityID
High	1
Medium	2
Low	3
None	4

This severityID is also loaded into my main table, which you had labelled 'Table'. 

2. I wrote the following DAX measure based on yours:

Measure for most severe prior service =

VAR Tab = SUMMARIZE ('Table', 'Table'[VisitID], "MinSeverity", MINX ( 'Table', 'Table'[SeverityID] ) )

RETURN COUNTX ( FILTER ( Tab , [MinSeverity] = MIN ( 'SeverityLU'[SeverityID] ) ) , [MinSeverity] )

The idea is that instead of using some DAX version of complex EXISTS statements in SQL, I just select the min severity ID of prior services for each VisitID. If it's 1, it's 1, if it's 2, it's 2. Then I want to perform a distinct count based on that. The result is faulty for "at least one high", "at least one medium", and "at least one low", but it is correct for "no prior services". Please advise. Thank you again!

[Rupak_bi · Accepted solution]

Hi @SoupyNoodles14 , change the Dax as below and check.

VAR Tab = SUMMARIZE ('Table', 'Table'[VisitID], "MinSeverity", MIN('Table'[SeverityID] ) )

[SoupyNoodles14]

Unfortunately, @Rupak_bi  that is giving the same incorrect results. What's happening is that PowerBI is somehow assigning more VisitIDs to 2 ("at least one medium") and 3  ("at least one low") than 1 or 2. When it comes to "none" there is nothing to choose from so it's correctly telling methe right number of visitIDs for that. Can you please advise. 

[SoupyNoodles14]

Nevermind - that message above is an oversight on my part. I had some hidden filters messing with my dashboard. This works perfectly, thank you again and have a great day!