Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
TriveniN
Helper II
Helper II

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

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

3 REPLIES 3
johnt75
Super User
Super User

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

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.