Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Heyang
Frequent Visitor

Table with a measure

I would like to create a table something like below

Heyang_0-1697768002509.png

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")}
Heyang_1-1697768297421.pngHeyang_2-1697768340895.png

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
1 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.

View solution in original post

6 REPLIES 6
Heyang
Frequent Visitor

HI,

 

basically i want to show a chart like this

Heyang_0-1698091271952.png

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

Heyang_1-1698091329792.png

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)

Heyang_2-1698091454730.png

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
 



what you show is not valid DAX.

 

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

Hi,

 

Thanks for the quick response.

 

Heyang_1-1698098810856.png

 

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,

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.

Thank you.

lbendlin
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.