topic Re: DAX FOR PLSQL in DAX Commands and Tips

DAX FOR PLSQL

SSWADOOD — Thu, 08 Jul 2021 09:46:02 GMT

Hello,

i am trying to convert

into dax

DATE_BETWEEN =

var SDATES= MIN('START_DATE'[START_DATE])

var EDATES= MAX('end_DATE'[end_DATE])

return

(

(DATESBETWEEN(DETAILS[EFF_DATE],SDATES,EDATES))

(DATESBETWEEN(DETAILS[EXPIRY_DATE],SDATES,EDATES))

(MAX([EFF_DATE])<=SDATES && MAX([EXPIRY_DATE])<=EDATES )

,1,0

)

where

sdate value is from after date slicer and

edate value is from before date slicer

and EFF_DATE and EXPIRY_DATE are two columns in my table visuals

when i add this measure to the table visual i get the error

MdxScript(Model) (14, 4) Calculation error in measure 'DETAILS'[DATE_BETWEEN]: A table of multiple values was supplied where a single value was expected.

can anybody help in this matter

Re: DAX FOR PLSQL

amitchandak — Thu, 08 Jul 2021 09:55:21 GMT

SSWADOOD , I think you need something like this

measure 2 =
VAR minDate = MINX( allselected('Calendar') , 'Calendar'[Date] )
VAR maxDate = MAXX(allselected('Calendar') , 'Calendar'[Date] )
RETURN

calculate(countrows(Table), filter(Table, (Table[term start Date] <=_max && Table[term start Date] >=_min) || (Table[term end Date] <=_max && Table[term end Date] >=_min)))

Re: DAX FOR PLSQL

SSWADOOD — Thu, 08 Jul 2021 10:56:04 GMT

Thanks amit,

Your measure works great , after updating it, got it to work on the inbetween date slicer.

measure 2 =

VAR minDate = MINX( allselected('START_DATE') , 'START_DATE'[START_DATE] )

VAR maxDate = MAXX(allselected('START_DATE') , START_DATE[start_DATE] )

RETURN

calculate

(

countrows(DETAILS),

filter (DETAILS,

(DETAILS[EFF_DATE] <= maxDATE && DETAILS[EFF_DATE] >= minDATE)

(DETAILS[EXPIRY_DATE] <= maxDATE && DETAILS[EXPIRY_DATE] >= minDATE)

(DETAILS[EFF_DATE]<= minDATE && DETAILS[EXPIRY_DATE] >= maxDATE)

)