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

   

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

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    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?