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] ) )

Rupak_bi's avatar
Rupak_bi
Super User
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..

 

  • SoupyNoodles14's avatar
    SoupyNoodles14
    Frequent Visitor
    2 years ago

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

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

      • 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!