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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LP280388
Resolver II
Resolver II

Variable in Calculated column not taking a value from nonrelated table

Hi Team,
 
I have the below Measure which results in Yes/No based on the condtions given.  Due to some reason I need to change this measure to a Calculated column so that it can be used in other measure calculations.  but i get a error "cant find name" for the Variables FirstDay and LastDay.   

Please help how to make this as a calculated column.

LP280388_0-1652879332326.png

 



column =
VAR FirstDay = CALCULATE(
MIN('From Date'[Date]),
ALLSELECTED('From Date'[Date]))
VAR LastDay = CALCULATE(
MAX('To Date'[Date]),
ALLSELECTED('To Date'[Date]))
Return
SWITCH(TRUE(),
MAX(VPR[AStartDate]) > LastDay, "No",
MAX(VPR[AStartDate]) >= FirstDay && Max(VPR[AEndDate]) <= LastDay,"Yes",
Max(VPR[AStartDate]) <= FirstDay && Max(VPR[AEndDate]) = BLANK(),"Yes",
MAX(VPR[AStartDate]) <= FirstDay && Max(VPR[AEndDate]) <= FirstDay,"No",
Max(VPR[AEndDate]) <= LastDay,"Yes",
MAX(VPR[AStartDate]) <= FirstDay && Max(VPR[AEndDate]) >= LastDay,"Yes",
Max(VPR[AEndDate]) = BLANK(),"Yes", "")
1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @LP280388

 

Try adding a filter() to the formula.

VAR FirstDay =
    CALCULATE (
        MIN ( 'From Date'[Date] ),
        FILTER ( ALLSELECTED ( 'From Date'[Date] )1 = 1 )
    )
VAR LastDay =
    CALCULATE (
        MAX ( 'To Date'[Date] ),
        FILTER ( ALLSELECTED ( 'From Date'[Date] )1 = 1 )
    )

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @LP280388

 

Try adding a filter() to the formula.

VAR FirstDay =
    CALCULATE (
        MIN ( 'From Date'[Date] ),
        FILTER ( ALLSELECTED ( 'From Date'[Date] )1 = 1 )
    )
VAR LastDay =
    CALCULATE (
        MAX ( 'To Date'[Date] ),
        FILTER ( ALLSELECTED ( 'From Date'[Date] )1 = 1 )
    )

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

Thank you very much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.