Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
107 | |
96 | |
92 | |
87 | |
69 |
User | Count |
---|---|
165 | |
130 | |
129 | |
102 | |
98 |