DAX - Frequency Count.

[amaleranda]

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. Here I have copied only one group name.

I have the DAX code for

Duration = DISTINCCOUNT(Table[Time])

I am struggling with getting frequency, which needs to count only when [Avil] column is 0. In the given sample dataset Frequncy should be 3 as [Avil] is 0 in three different time slots.

Output should look like(when filter out only to show [Avil] = 0). Below I copied the data.

Group             Duration       Frequency

PP24634         11                    3        

Time	Group	Avil
13:01	PP24634	1
13:02	PP24634	0
13:03	PP24634	0
13:04	PP24634	0
13:05	PP24634	1
13:06	PP24634	1
13:07	PP24634	1
13:08	PP24634	1
13:09	PP24634	1
13:10	PP24634	1
13:11	PP24634	1
13:12	PP24634	0
13:13	PP24634	0
13:14	PP24634	0
13:15	PP24634	1
13:16	PP24634	1
13:17	PP24634	1
13:18	PP24634	1
13:19	PP24634	1
13:20	PP24634	1
13:21	PP24634	1
13:22	PP24634	1
13:23	PP24634	0
13:24	PP24634	0
13:25	PP24634	0
13:26	PP24634	0
13:27	PP24634	0
13:28	PP24634	1
13:29	PP24634	1
13:30	PP24634	1

[kpitzen]

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? 

[v-frfei-msft · Accepted solution]

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