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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Issue

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

Status: Needs Info

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

 

Comments
v-mengzhu-msft
Community Support
Status changed to: Needs Info

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
Helper II

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)