Issue

[Alice_Cooper]

Feedback Type:
Frown (Error)

Timestamp:
2022-12-05T04:58:37.7882793Z

Local Time:
2022-12-05T14:58:37.7882793+10:00

Session ID:
9dab4a04-5fa9-4530-95f4-74a29248a65d

Release:
November 2022

Product Version:
2.111.590.0 (22.11) (x64)

OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-GB)

CLR Version:
4.8 or later [Release Number = 528372]

Peak Virtual Memory:
72.7 GB

Private Memory:
2.03 GB

Peak Working Set:
2.13 GB

IE Version:
11.789.19041.0

User ID:
eeebfb0f-f800-4565-a54d-0f0bdef83d00

Workbook Package Info:
1* - en-AU, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Model Default Mode:
Import

Model Version:
PowerBI_V3

Enabled Preview Features:
PBI_enableWebView2
PBI_sparklines
PBI_scorecardVisual
PBI_fieldParametersSuperSwitch

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_SpanishLinguisticsEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_compositeModelsOverAS
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PQ_WebView2Connector
PBI_NlToDax
PBI_horizontalFusion
PBI_optimizeTabRibbon
PBI_relationshipEditPane

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
100%

Supported Services:
Power BI

[Anonymous]

Hi @Alice_Cooper ,

Need more information, for example, what operation were you doing when the above error occurred?

Best regards,

Community Support Team Selina zhu

[Alice_Cooper]

My data is as follows:

The (Effective Lease End Dates) for EOMRPT 30/11/2022 falls in two calendar years 2024 & 2023 these are valid.

The same is for EOMRPT of 31/03/23 & 31/05/23

EOMRPT	Lease Start Date	Effective Lease End Date	CalYearLeaseEndDate
31/07/2022	7/07/2022	30/06/2023	2023
31/07/2022	10/07/2022	10/01/2023	2023
31/08/2022	2/08/2022	31/07/2023	2023
31/08/2022	5/08/2022	5/08/2023	2023
30/09/2022	1/09/2022	1/09/2023	2023
30/09/2022	9/09/2022	9/09/2023	2023
30/09/2022	15/09/2022	15/09/2023	2023
30/11/2022	11/11/2022	11/05/2024	2024
30/11/2022	22/11/2022	22/05/2023	2023
31/01/2023	4/01/2023	4/01/2024	2024
31/01/2023	5/01/2023	5/07/2023	2023
31/01/2023	10/01/2023	10/01/2024	2024
28/02/2023	2/02/2023	2/08/2024	2024
28/02/2023	4/02/2023	4/08/2024	2024
31/03/2023	1/03/2023	1/09/2023	2023
31/03/2023	2/03/2023	2/09/2023	2023
31/03/2023	4/03/2023	4/09/2023	2023
31/03/2023	5/03/2023	5/03/2024	2024
30/04/2023	1/04/2023	1/04/2024	2024
31/05/2023	2/05/2023	2/05/2024	2024
31/05/2023	4/05/2023	4/05/2024	2024
31/05/2023	5/05/2023	5/11/2023	2023
30/06/2023	1/06/2023	1/12/2023	2023
30/06/2023	2/06/2023	2/12/2023	2023
31/07/2023	2/07/2023	2/07/2024	2024
31/07/2023	3/07/2023	3/01/2024	2024
31/07/2023	4/07/2023	4/01/2024	2024
31/08/2023	1/08/2023	1/02/2024	2024
31/08/2023	2/08/2023	2/02/2024	2024
30/09/2023	1/09/2023	1/03/2024	2024

The code :

Out-Properties with Lease End Date in Calendar Year = CALCULATE (

    DISTINCTCOUNT ( Leases[Property ID] ),

    FILTER (

        ALL ( Leases ),

        Leases[EOMRPT]

            >= MINX (

                FILTER (

                    ALL ( Leases ),

                    Leases[CalYearLeaseEndDate]

                        = LOOKUPVALUE (

                            Leases[CalYearLeaseEndDate],

                            Leases[EOMRPT], MAX ( EOMRPT[EOMRPT] )

                        )

                ),

                Leases[EOMRPT]

            )

            && year(Leases[EOMRPT]) <= MAX (EOMRPT[EOMRPT] )

            && Leases[CalYearLeaseEndDate]

                = LOOKUPVALUE (

                    Leases[CalYearLeaseEndDate],

                    Leases[EOMRPT], MAX ( EOMRPT[EOMRPT])

                )

    )

)

Error Message:

MdxScript(Model) (252, 21) Calculation error in measure 'Leases'[Out-Properties with Lease End Date in Calendar Year]: A table of multiple values was supplied where a single value was expected.

But the data is correct …

If we select EOMRPT as 30/11/2022 we want to test if the CalYearLeaseEndDate is equal to 2022 in this case, it should return Zero.

If If we select EOMRPT as 31/03/2023 we want to test if the CalYearLeaseEndDate is equal to 2023 in this case, it should return three.

OK Miguel.

This is what I need:

If I select the EOMRPT as 31/03/23 I want to be able to count how many leases started in the calendar year and how many ended in the calendar year.

I should get 9 Started

(IN-Properties with Lease Start Date in Calendar Year)

and 12 Ended

(Out-Properties with Lease End Date in Calendar Year)

…. Therefore, I can say that I have 3 (12-9) active leases…

Bal-Active Leases During Calendar Year = (Out-Properties with Lease End Date in Calendar Year)- (Out-Properties with Lease End Date in Calendar Year)