Count and DAX

[Anonymous]

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.

[Anonymous]

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.

[tamerj1]

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

[FreemanZ]

hi @Anonymous 

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

[Anonymous]

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.