lookup boolean value from another table

[nick9one1]

I have two tables and using this DAX to import values from one table to the other;

(this works)

Total Rooms = 
CALCULATE(
    MAX('Lettings Fact_SPVDetails_Summary'[TotalRooms]),
    FILTER(
        ALL('Lettings Fact_SPVDetails_Summary'),
        [SPV]=Earlier('Lettings Fact_Lettings_Summary'[SPV])&&
        [Academic_Year]=Earlier('Lettings Fact_Lettings_Summary'[Academic_Year])
    )
)

Most of the values are numerical, but I have a boolean column I also need to bring across (Nominated). 
Calulate obviously does not work with boolean values, so what do I replace it with? 

Nominated = 
CALCULATE(
    MAX('Lettings Fact_SPVDetails_Summary'[Nominated]),
    FILTER(
        ALL('Lettings Fact_SPVDetails_Summary'),
        [SPV]=Earlier('Lettings Fact_Lettings_Summary'[SPV])&&
        [Academic_Year]=Earlier('Lettings Fact_Lettings_Summary'[Academic_Year])
    )
)

thank you

[v-zhangti · Accepted solution]

Hi, @nick9one1 

I simulated some data to restore your problem, you can use the lookupvalue formula to see the boolean value.

Total Rooms = 
LOOKUPVALUE (
    'Lettings Fact_SPVDetails_Summary'[TotalRooms],
    'Lettings Fact_SPVDetails_Summary'[SPV], [SPV],
    'Lettings Fact_SPVDetails_Summary'[Academic_Year], [Academic_Year]
)

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @nick9one1 

try to use LOOKUPVALUE, like this:

Nominated =
LOOKUPVALUE(
    'Lettings Fact_SPVDetails_Summary'[Nominated],
    'Lettings Fact_Lettings_Summary'[SPV],
    [SPV],
    'Lettings Fact_Lettings_Summary'[Academic_Year],
    [Academic_Year]
)

if the two tables are connected one-many, this shall be enough:

Nominated = RELATED('Lettings Fact_SPVDetails_Summary'[Nominated])

[amitchandak]

@nick9one1 , try like

Nominated =
CALCULATE(
MAXX('Lettings Fact_SPVDetails_Summary', int('Lettings Fact_SPVDetails_Summary'[Nominated])),
FILTER(
('Lettings Fact_SPVDetails_Summary'),
[SPV]=('Lettings Fact_Lettings_Summary'[SPV])&&
[Academic_Year]=('Lettings Fact_Lettings_Summary'[Academic_Year])
)
)=1