cancel
Showing results 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

Frequent Visitor

## Table with a measure

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?

1 ACCEPTED SOLUTION
Super User

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.

6 REPLIES 6
Frequent Visitor

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

Super User

what you show is not valid DAX.

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

Frequent Visitor

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?

Kind regards,

Super User

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.

Frequent Visitor

Thank you.

Super User

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

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors