grouping in a scalar value measure

[Rngomoa]

Am trying to create a dashboard by counting the total patient_id (Greencard[patient_id]), for clients whose visit dates (Greencard[visit_date]) are less than the selected months (CalenderDate[dates]) and their visit dates are less than the exit date (Greencard[ExitDate1]).

 The first part of the dax works out fine. However, it has duplicates. So, my problem is the second part of the dax (i.e begining from COUNTX, which tries to remove those duplicates. Am getting an error. I would appreciate any assistance to remove the duplicates.

Thanks.

tx_curr =

var curdate=MAX(CalenderDate[dates])

var tx_curr2=CALCULATE(COUNT(Greencard[patient_id]),FILTER(Greencard,Greencard[visit_date]<=curdate && Greencard[visit_date]<=Greencard[ExitDate1]))

return

COUNTX(GROUPBY(Greencard,Greencard[patient_id],Greencard[mflcode],"Tx_curr3",maxx(CURRENTGROUP(),tx_curr2)),Greencard[patient_id])

[BeaBF · Accepted solution]

@Rngomoa Hi!

The issue with the second part of your DAX formula is that the COUNTX function is trying to count the distinct patient IDs in the result of the GROUPBY function, but the GROUPBY function is not returning a table with distinct patient IDs because the MAXX function is being used as an aggregator.

tx_curr =
VAR curdate = MAX(CalenderDate[dates])
RETURN
COUNTX(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
Greencard,
Greencard[patient_id],
Greencard[mflcode],
"tx_curr2", CALCULATE(COUNT(Greencard[patient_id]), Greencard[visit_date] <= curdate && Greencard[visit_date] <= Greencard[ExitDate1])
),
"tx_curr3", IF([tx_curr2] > 0, 1, 0)
),
[tx_curr3] > 0
),
DISTINCT(Greencard[patient_id])
)

BBF