Error on Multiple date selection

[Kay_Kalu]

I have this table with a rate calculation want to be able to select mutiple date and not get an error

Table image

The dax use to generate the rate is 

TerminationsCount = COUNTROWS(P_MDT002_TERMINATIONS_LAPSES) / ActivePolCount = countrows(FILTER(P_MDT000_MASTER,P_MDT000_MASTER[EFFECTIVE_DATE]<=ALLSELECTED('LIGHTHOUSE_CALENDAR'[SelectionDate])&&RELATED('REF070_PRODUCT_TAXONOMY'[MIGRATION_DATE])<=ALLSELECTED('LIGHTHOUSE_CALENDAR'[SelectionDate])&&or(P_MDT000_MASTER[STATUS]<6000,and(P_MDT000_MASTER[STATUS]>=6000,P_MDT000_MASTER[STAT_DATE]>ALLSELECTED('LIGHTHOUSE_CALENDAR'[SelectionDate])))))

I did build the dashboard but have to resovlve this issue any Ideas I know it might be diffcult to really understand but I can't share more than this.

[v-vpabbu]

Hi @Kay_Kalu,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[HarishKM · Accepted solution]

@Kay_Kalu Hey,
Can you please below measure?

TerminationRate =
VAR SelectedDate = MAX('LIGHTHOUSE_CALENDAR'[SelectionDate])

VAR TerminationsCount = COUNTROWS(P_MDT002_TERMINATIONS_LAPSES)

VAR ActivePolCount =
COUNTROWS(
FILTER(
P_MDT000_MASTER,
P_MDT000_MASTER[EFFECTIVE_DATE] <= SelectedDate &&
RELATED('REF070_PRODUCT_TAXONOMY'[MIGRATION_DATE]) <= SelectedDate &&
(
P_MDT000_MASTER[STATUS] < 6000 ||
(
P_MDT000_MASTER[STATUS] >= 6000 &&
P_MDT000_MASTER[STAT_DATE] > SelectedDate
)
)
)
)

RETURN
DIVIDE(TerminationsCount, ActivePolCount, 0)

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[bhanu_gautam]

@Kay_Kalu , Try using

dax
TerminationsCount =
VAR SelectedDates = ALLSELECTED('LIGHTHOUSE_CALENDAR'[SelectionDate])
RETURN
COUNTROWS(
FILTER(
P_MDT002_TERMINATIONS_LAPSES,
P_MDT002_TERMINATIONS_LAPSES[TERMINATION_DATE] IN SelectedDates
)
)

ActivePolCount =
VAR SelectedDates = ALLSELECTED('LIGHTHOUSE_CALENDAR'[SelectionDate])
RETURN
COUNTROWS(
FILTER(
P_MDT000_MASTER,
P_MDT000_MASTER[EFFECTIVE_DATE] <= MAX(SelectedDates) &&
RELATED('REF070_PRODUCT_TAXONOMY'[MIGRATION_DATE]) <= MAX(SelectedDates) &&
(
P_MDT000_MASTER[STATUS] < 6000 ||
(
P_MDT000_MASTER[STATUS] >= 6000 &&
P_MDT000_MASTER[STAT_DATE] > MAX(SelectedDates)
)
)
)
)

[Kay_Kalu]

Thanks for help but this hasn't work got this error on the ActivePolCount  dax "The MAX function only accepts a column reference as an argument." tried removing the max function but the multiple date selection still came up with an error.

For context the selectedDate dax 

"SelectionDate = IF(AND('LIGHTHOUSE_CALENDAR'[Month_Year]>=DATE(2015,01,01),'LIGHTHOUSE_CALENDAR'[Month_Year]<=EDATE(TODAY(),-1)),'LIGHTHOUSE_CALENDAR'[Month_Year])"

this might help in understanding the build.

Thanks

[v-vpabbu]

Hi @Kay_Kalu,

Comparison operators like <= require a single scalar value.

Use an iterator function like MAXX, which evaluates a column over the table returned by ALLSELECTED and returns a single value.

Regards,

Vinay Pabbu