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