topic Re: Please HELP Count skips occurrences in DAX Commands and Tips

Please HELP Count skips occurrences

NilR — Fri, 06 May 2022 21:24:04 GMT

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!

Re: Please HELP Count skips occurrences

amitchandak — Sat, 07 May 2022 01:10:04 GMT

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

Re: Please HELP Count skips occurrences

OwenAuger — Sat, 07 May 2022 01:18:03 GMT

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

Re: Please HELP Count skips occurrences

NilR — Sat, 07 May 2022 02:48:17 GMT

It was brilliant ! Thank you!!

Re: Please HELP Count skips occurrences

NilR — Sat, 07 May 2022 04:09:52 GMT

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