A table multiple values was supplied where single value was expected.

[TriveniN]

Getting an issue  when I include below measure in drill through report.

Error details : A table multiple values was supplied where single value was expected.

Days_In_Period_% =

-- Current Year Month End

VAR _dateM = CALCULATE(MAXX(Datecalendar,ENDOFMONTH(Datecalendar[TheDate])), ALLSELECTED ( ) )

VAR _dateQ = CALCULATE(MAXX(Datecalendar,ENDOFQUARTER(Datecalendar[TheDate])), ALLSELECTED ( ) )

-----------------------

--- Same Store End Date Month

VAR _ssdateM = CALCULATE(MAXX(Datecalendar,ENDOFMONTH(DATEADD(Datecalendar[TheDate],-13,MONTH))), ALLSELECTED ( ) )

--- Same Store End Date Quarter

VAR _ssdateQ = CALCULATE(MAXX(Datecalendar,ENDOFQUARTER(DATEADD(Datecalendar[TheDate],-5,QUARTER))),ALLSELECTED ( ) )

-----------------------

VAR _dateDMIN = CALCULATE(MINX(Datecalendar,STARTOFMONTH(Datecalendar[TheDate])), ALLSELECTED ( ) )

-- Current Year Selected Quarter

VAR _dateQMIN =  CALCULATE(MINX(Datecalendar,STARTOFQUARTER(Datecalendar[TheDate])), ALLSELECTED ( ) )

VAR _SSdateDMIN = CALCULATE(MINX(Datecalendar,STARTOFMONTH(DATEADD(Datecalendar[TheDate],-13,MONTH))), ALLSELECTED ( ) )

-- Current Year Selected Quarter

VAR _SSdateQMIN =    CALCULATE(MINX(Datecalendar,STARTOFQUARTER(DATEADD(Datecalendar[TheDate],-5,QUARTER))),ALLSELECTED ( ) )

var _SSdateMIN =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month", _SSdateDMIN,

    IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter", _SSdateQMIN) )

var _date =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month", _dateM,

    IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter", _dateQ) )

var _SSdate =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month", _ssdateM,

    IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter", _ssdateQ) )

var _dateMIN =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month", _dateDMIN,

    IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter", _dateQMIN) )

VAR _TP_AvgOcc_Name =

CALCULATE( COUNT(LEASE_SFV[Name]),

 LEASE_SFV[Yardi_Lease_Status__c] IN {"Past","Current","Notice"} &&

LEASE_SFV[Lease_Type__c] IN {"Initial","Initial Lease Assumption","Initial Leaseback"} &&  

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Month",

FORMAT(LEASE_SFV[Lease_Start_Date__c],"YYYYMM") <= FORMAT(_date,"YYYYMM"),

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Quarter",

FORMAT(LEASE_SFV[Lease_Start_Date__c],"YYYYQ") <= FORMAT(_date,"YYYYQ")) ), --&&

( LEASE_SFV[Move_Out_Date__c] = BLANK() ||

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Month",

FORMAT(LEASE_SFV[Move_Out_Date__c],"YYYYMM") >= FORMAT(_dateMIN,"YYYYMM"),

IF(SELECTEDVALUE(ReportPeriod[Type]) = "Quarter",

FORMAT(LEASE_SFV[Move_Out_Date__c],"YYYYQ") >= FORMAT(_dateMIN,"YYYYQ")) ) ) ,

FILTER(ACQUIRED_PROPERTY_SFV,

(ACQUIRED_PROPERTY_SFV[Disposition_Date__c] = BLANK() ||

ACQUIRED_PROPERTY_SFV[Disposition_Date__c] > _date) ) )

VAR _SS_AvgOcc_Name =

CALCULATE( DISTINCTCOUNT(LEASE_SFV[Name 1] ),

LEASE_SFV[Lease_Type__c] IN {"Initial"}

&&  

NOT LEASE_SFV[Yardi_Lease_Status__c] IN {"Canceled" , "pending"}

 ,

FILTER(ACQUIRED_PROPERTY_SFV,

(ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <> BLANK() &&  

ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <= _SSdate )

&& (ACQUIRED_PROPERTY_SFV[Disposition_Date__c] = BLANK() ||

ACQUIRED_PROPERTY_SFV[Disposition_Date__c] > _SSdate) )

)

VAR _SS_AvgOcc_Name_ =

CALCULATE( VALUES(LEASE_SFV[Name 1] ),

LEASE_SFV[Lease_Type__c] IN {"Initial"}

&&  

NOT LEASE_SFV[Yardi_Lease_Status__c] IN {"Canceled" , "pending"}

 ,

FILTER(ACQUIRED_PROPERTY_SFV,

(ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <> BLANK() &&  

ACQUIRED_PROPERTY_SFV[Stabilization_Date__c] <= _SSdate )

&& (ACQUIRED_PROPERTY_SFV[Disposition_Date__c] = BLANK() ||

ACQUIRED_PROPERTY_SFV[Disposition_Date__c] > _SSdate) )

)

VAR _TPDIP=  

IF( SELECTEDVALUE(ReportPeriod[Type])="Month",

  DATEDIFF(CALCULATE(STARTOFMONTH(Datecalendar[TheDate])), _dateM+1,DAY),

IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter",

  DATEDIFF(CALCULATE(STARTOFQUARTER(Datecalendar[TheDate])), _dateQ+1, DAY)) )

  VAR _SSDIP =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month" &&

VALUES(LEASE_SFV[Name 1]) IN {_SS_AvgOcc_Name_},

  DATEDIFF(CALCULATE(STARTOFMONTH(Datecalendar[TheDate])), _dateM+1,DAY),

IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter" &&

VALUES(LEASE_SFV[Name 1]) IN {_SS_AvgOcc_Name_},

  DATEDIFF(CALCULATE(STARTOFQUARTER(Datecalendar[TheDate])), _dateQ+1, DAY)) )

RETURN

SWITCH( TRUE(),

SELECTEDVALUE(TotalProtfolio_Rows[Key]) = 1 && SELECTEDVALUE(ReportType[Key]) = 1  &&  _TP_AvgOcc_Name <> Blank (), _TPDIP,

SELECTEDVALUE(TotalProtfolio_Rows[Key]) = 1 && SELECTEDVALUE(ReportType[Key]) = 2 &&   _SS_AvgOcc_Name <> Blank (), _SSDIP,

BLANK())

Please help me out, If any one has an idea

[johnt75]

I think the problem is probably in _SS_AvgOcc_Name_. VALUES returns a table but your code is expecting it to only have 1 row. My guess is that multiple rows are being returned. You could check by changing the measure to return COUNTROWS(_SS_AvgOcc_Name_).

[TriveniN]

Thanks for your response

But, I need to compare values that are comming from _SS_AvgOcc_Name_ to Lease_SFV[Name] while calculating below one

 VAR _SSDIP =

IF( SELECTEDVALUE(ReportPeriod[Type])="Month" &&

VALUES(LEASE_SFV[Name 1]) IN {_SS_AvgOcc_Name_},

  DATEDIFF(CALCULATE(STARTOFMONTH(Datecalendar[TheDate])), _dateM+1,DAY),

IF(SELECTEDVALUE(ReportPeriod[Type])="Quarter" &&

VALUES(LEASE_SFV[Name 1]) IN {_SS_AvgOcc_Name_},

  DATEDIFF(CALCULATE(STARTOFQUARTER(Datecalendar[TheDate])), _dateQ+1, DAY)) )

Can you guide me how we can achive

[johnt75]

You could try

• change CALCULATE( VALUES( to CALCULATETABLE(VALUES
• remove the {} from around the variable when you're doing the IN check
• in the final SWITCH statement, change the check for <> BLANK to NOT( ISEMPTY(  _SS_AvgOcc_Name ) )