topic Table with a measure in DAX Commands and Tips

Table with a measure

Heyang — Fri, 20 Oct 2023 02:20:41 GMT

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

Re: Table with a measure

lbendlin — Sun, 22 Oct 2023 00:34:46 GMT

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

Re: Table with a measure

Heyang — Mon, 23 Oct 2023 20:18:14 GMT

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

Re: Table with a measure

lbendlin — Mon, 23 Oct 2023 20:37:14 GMT

what you show is not valid DAX.

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

Re: Table with a measure

Heyang — Mon, 23 Oct 2023 22:56:25 GMT

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,

Re: Table with a measure

lbendlin — Mon, 23 Oct 2023 23:02:13 GMT

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.

Re: Table with a measure

Heyang — Tue, 24 Oct 2023 01:28:32 GMT

Thank you.