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 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?

thanks in advance
DAX
measure
Table

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

  • 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
      Super 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,