Forum Discussion

amaleranda's avatar
amaleranda
Post Patron
7 years ago
Solved

DAX - Frequency Count.

Hi DAX gurus,   I want to count frequency  and duration of the group. Both needs to count when [Avil] columns = 0.(I can achieve this by using a page/visual level filter.there are multiple groups. ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi amaleranda ,

     

    1. To create a calculated column as below.

     

    Column = 
CALCULATE (
    SUM ( 'Table'[Avil] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Time] <= EARLIER ( 'Table'[Time] )
            && 'Table'[Group] = EARLIER ( 'Table'[Group] )
    )
)

    2.Then we can create the Frequency measure.

    Frequency = CALCULATE(DISTINCTCOUNT('Table'[Column]),FILTER('Table','Table'[Avil] = 0))

     

kpitzen's avatar
kpitzen
Regular Visitor
3 years ago

how do we allow this result set to still be filtered by a dimension? for example a date table. I am trying to get to the count of hospitalizations per patient (within a given selected timeframe) - and then count distinct patients in buckets of "1 hospitalization", "2 hospitalizations", "3 or more" etc. So I am trying to first get a rolled up value of 'hospitalizations' in the selected date range using the above approach

Column =
 var dMin = FIRSTDATE(ALLSELECTED('Service Date'[Service Date]))
  var dMax = LASTDATE(ALLSELECTED('Service Date'[Service Date]))
 return
 CALCULATE (
    DISTINCTCOUNT( 'Hospitalizations'[UniqueHospitalization] ),
    FILTER (
       ALL ( 'Hospitalizations' ),
      'Hospitalizations'[Person Served Key] = EARLIER ( 'Hospitalizations'[Person Served Key]) && Hospitalizations[Hospitalization Begin Date]>= dmin &&  Hospitalizations[Hospitalization Begin Date] <=dMax
    )
)

 
without the date range variables, the column brings back the count of hospitalizations in the whole fact table. With these dates, the date range is just the max and min of the date table (not taking into account slicer selections). Any advice on my date dilemma?