Forum Discussion

nick9one1's avatar
nick9one1
Icon for Helper III rankHelper III
3 years ago
Solved

lookup boolean value from another table

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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])

     

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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.