The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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
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
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
It was brilliant ! Thank you!!
@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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
28 | |
17 | |
16 | |
15 |
User | Count |
---|---|
51 | |
49 | |
37 | |
32 | |
28 |