Forum Discussion

Heyang's avatar
Heyang
Frequent Visitor
2 years ago
Solved

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 l...
DAX
measure
Table
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

Heyang's avatar
Heyang
Frequent Visitor
2 years ago

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
 



  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    what you show is not valid DAX.

     

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

    • Heyang's avatar
      Heyang
      Frequent Visitor
      2 years ago

      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,

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        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.