Forum Discussion
Create Dynamic Measure of Distinct Counts
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.
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 belowMeasure =Var Tab = SUMMARIZE('Table','Table'[VisitID],"Count",max(0,countx(filter('Table','Table'[PriorServiceID]<>"NULL"),'Table'[PriorServiceID])))returncountx(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..Hi SoupyNoodles14 , change the Dax as below and check.
VAR Tab = SUMMARIZE ('Table', 'Table'[VisitID], "MinSeverity", MIN('Table'[SeverityID] ) )
8 Replies
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 belowMeasure =Var Tab = SUMMARIZE('Table','Table'[VisitID],"Count",max(0,countx(filter('Table','Table'[PriorServiceID]<>"NULL"),'Table'[PriorServiceID])))returncountx(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..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?
You can do it same way as previous one. Let me know if you stuck.
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.
