topic Re: lookup boolean value from another table in DAX Commands and Tips

lookup boolean value from another table

nick9one1 — Wed, 11 Jan 2023 13:07:20 GMT

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

Re: lookup boolean value from another table

amitchandak — Wed, 11 Jan 2023 15:49:42 GMT

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

Re: lookup boolean value from another table

FreemanZ — Thu, 12 Jan 2023 06:14:22 GMT

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

Re: lookup boolean value from another table

v-zhangti — Thu, 12 Jan 2023 06:42:12 GMT

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.