Please HELP Count skips occurrences

[NilR]

Hi,

I have a count Calculated Column that skips numbers if there are more than one YearMonth Value. Do you have any better suggestion?

Active_records_last_22_months = 
VAR _GP='Enrollment'[GP]
VAR _YRMO='Enrollment'[YEARMONTH]
var _id = 'Enrollment'[ID]
var end_date = 'Enrollment'[start_date]
var start_date = EOMONTH(end_date,-23)+1

var count_occurences = 
CALCULATE(COUNT ('Enrollment'[YEARMONTH]),
        FILTER('Enrollment', 
        'Enrollment'[GP]= _GP &&
        'Enrollment'[ID]=_id &&
         'Enrollment'[start_date] >= start_date &&
          'Enrollment'[start_date] <=end_date))

return
count_occurences

Thank you!

[OwenAuger · Accepted solution]

Hi @NilR 

A small change to your existing code that would fix it would be to change COUNT to DISTINCTCOUNT.

If performance is a concern, you may want to consider using RANKX as discussed in the article

How to compute index numbers at top speed.

Here are a couple of options using RANKX:

Active_records_last_22_months = 
VAR end_date = 'Enrollment'[start_date]
VAR start_date =
    EOMONTH ( end_date, -23 ) + 1
VAR count_occurences =
    RANKX (
        CALCULATETABLE (
            VALUES ( 'Enrollment'[YEARMONTH] ),
            ALLEXCEPT ( Enrollment, Enrollment[GP], Enrollment[ID] ),
            'Enrollment'[start_date] >= start_date && 'Enrollment'[start_date] <= end_date
        ),
        Enrollment[YEARMONTH],
        ,
        ASC
    )
RETURN
    count_occurences

Active_records_last_22_months = 
VAR _GP = 'Enrollment'[GP]
VAR _YRMO = 'Enrollment'[YEARMONTH]
VAR _id = 'Enrollment'[ID]
VAR end_date = 'Enrollment'[start_date]
VAR start_date =
    EOMONTH ( end_date, -23 ) + 1
VAR count_occurences =
    RANKX (
        FILTER (
            'Enrollment',
            'Enrollment'[GP] = _GP
                && 'Enrollment'[ID] = _id
                && 'Enrollment'[start_date] >= start_date
                && 'Enrollment'[start_date] <= end_date
        ),
        Enrollment[YEARMONTH],
        ,
        ASC,
        Dense
    )
RETURN
    count_occurences

Regards,

Owen

[NilR]

I have another quick question.

I want to use this code and CREATE SECOND Column but use filter from another table, but I received below error:

A single value for column 'YEARMONTH' in table 'Enrollment' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Here is the updated code:

Active_records_last_22_months = 
VAR _GP = 'CLAIM'[GP]
VAR _YRMO = 'CLAIM'[YEARMONTH]
VAR _id = 'CLAIM'[ID]
VAR end_date = 'CLAIM'[start_date]
VAR start_date =
    EOMONTH ( end_date, -23 ) + 1
VAR count_occurences =
    RANKX (
        FILTER (
            'Enrollment',
            'Enrollment'[GP] = _GP
                && 'Enrollment'[ID] = _id
                && 'Enrollment'[start_date] >= start_date
                && 'Enrollment'[start_date] <= end_date
        ),
        Enrollment[YEARMONTH],
        ,
        ASC,
        Dense
    )
RETURN
    count_occurences

[NilR]

It was brilliant ! Thank you!!

[amitchandak]

@NilR , Try dense Rank

A new column

Rankx(Table, [YearMonth],,asc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns