Forum Discussion

SoupyNoodles14's avatar
SoupyNoodles14
Frequent Visitor
2 years ago
Solved

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...
  • Rupak_bi's avatar
    Rupak_bi
    2 years ago

    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..

     

  • Rupak_bi's avatar
    Rupak_bi
    2 years ago

    Hi SoupyNoodles14 , change the Dax as below and check.

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

SoupyNoodles14's avatar
SoupyNoodles14
Frequent Visitor
2 years ago

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. 

SeverityLiteralSeverityID
High1
Medium2
Low3
None4

 

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's avatar
Rupak_bi
Super User
2 years ago

Hi SoupyNoodles14 , change the Dax as below and check.

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

  • SoupyNoodles14's avatar
    SoupyNoodles14
    Frequent Visitor
    2 years ago

    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's avatar
      SoupyNoodles14
      Frequent Visitor
      2 years ago

      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!