The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
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 |
---|---|
58 | |
32 | |
20 | |
16 | |
15 |
User | Count |
---|---|
79 | |
32 | |
30 | |
24 | |
21 |