cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors