Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Count and DAX

Hi Guys, I am facing issue counting the below rows where the condition is like:

Count number of rows for each group name where

Open date <= Date and resolve date is >= Date. So basically the count for 01-01-2020 would be 30 and for 02-01-2020 should also be 30 even though there is no open date or resolve date for that day.

I have created this table with required columns from the calendar and the fact table.

 

Appreciate your help in advance.

 

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Anonymous 

    unable to follow. Could you elaborate how you get 30 in your example date, say 01-01-2020?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      So the count is basically 30 since the open date is equal to Date and resolve dates are greater than Date. So count of all the tickets falling as per above criteria is 30 for that group name.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 
    For a calculated column please use

    Count =
COUNTROWS (
    FILTER (
        CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[FactTable.Group Name] ) ),
        'Table'[FactTable.Open Date] <= 'Table'[Date]
            && 'Table'[FactTable.Resolve Rate] >= 'Table'[Date]
    )
)

    For a measure you can use

    Count =
COUNTROWS (
    FILTER (
        'Table',
        'Table'[FactTable.Open Date] <= 'Table'[Date]
            && 'Table'[FactTable.Resolve Rate] >= 'Table'[Date]
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create measures.

    Measure =
CALCULATE (
    COUNT('Table'[date]),
    FILTER (
        ALL ( 'Table' ),
        'Table'[date] = SELECTEDVALUE ( 'Table'[open date] )
            && 'Table'[date] <= SELECTEDVALUE ( 'Table'[close date] )
            && 'Table'[date] = SELECTEDVALUE ( 'Table'[date] )
    )
)

    Measure 2 = COUNTAX(FILTER(ALL('Table'),[Measure]<>BLANK()),[Measure])

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.