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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.