Forum Discussion

SSWADOOD's avatar
SSWADOOD
Helper I
5 years ago
Solved

DAX FOR PLSQL

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

IF
(
(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

  • amitchandak's avatar
    amitchandak
    5 years ago

    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)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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)))

    • SSWADOOD's avatar
      SSWADOOD
      Helper I
      5 years ago

      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)
         )
      )