Forum Discussion

Kay_Kalu's avatar
Kay_Kalu
Helper I
1 year ago
Solved

Error on Multiple date selection

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_...
Need Help
  • HarishKM's avatar
    HarishKM
    1 year ago

    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's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
Kay_Kalu
Helper I
1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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