Table with a measure

[Heyang]

I would like to create a table something like below

year is set for 2006, 2013 and 2018, and i can use measure like lookupvalue to find year's number of people. but the problem is Dax doesnt like measure in the created table
my dax is like 

table = {(2006,LOOKUPVALUE(Census_all[2006Total], Census_all[Area Code],[selectedClub]),

    (2013,"456"),

    (2018,"789")}

you can see the data is there but its table doesnt like measure

and basically 2013 and 2018 will show data but 2006 it cannot, becasue selectedClub is a measure.

i know where is a function is like calcuated or something could solve the problem but i tried and cannot solve it.
any suggestions?

thanks in advance

[Heyang]

HI,

basically i want to show a chart like this

and i converted to the table and i understand the data structure should looks like below

and this is how my table looks like, and i have a slicer the user could select a area which is link to the areacode to the table (selected area is a measure)

and i want to recreate a table use the dax

{

    (2006,"row1",LOOKUPVALUE(Census_all[2006Total], Census_all[Area Code],[selected area]),

    (2013,"row2",LOOKUPVALUE(StudyArea[areaCode], StudyArea[club], "Bethells Beach SLSP")),

    (2018,"row3","123")

    }

but in the table, year 2006 wouldnt show the value because i think [selected area] is a measure.

year 2013 and 2018 are fine.

How to solve the problem

Thanks in advance

[lbendlin]

what you show is not valid DAX.

You can use COALESCE to handle scenarios when measures return BLANK()

[Heyang]

Hi,

Thanks for the quick response.

I am trying to create a new table, and you can see row1 value3 isnt shown up. I guess the reason is [areaCodeForSelectedClub] is a measure and PowerBI doesnt like its not been static value?

and how do I fix it?

thanks in advance

Kind regards,

[lbendlin · Accepted solution]

Don't use LOOKUPVALUE unless you really have to.

Use proper table definitions DATATABLE function - DAX | Microsoft Learn

Implement your business logic as a calculated column rather than a measure, or use COALESCE.

[Heyang]

Thank you.

[lbendlin]

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?