Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using a Measure in SELECTCOLUMNS

Starting out with Power BI.

Struggling with what can be condensed down to the following.

Have a table called Adults.  Two columns Name and Age.

Have a table called Children, same structure as Adults.

Adults contains Bob, 50 and Sue, 35.

Children contains Andy,15 and Mary 10.

I have a parameter called pAge and a corresponding slicer.  I also have a measure mAge defined as part of the Adults table as 

mAge = pAge[pAge Value].
I want to merge the two tables and in doing so update the Age field for Bob's record to the value of the slicer.  Seems straing-forward enough.  The following code does work.
 
Family = SELECTCOLUMNS(union(Adults,'Children'),"Name",[Name],"Age",IF([Name]="Bob",75,[Age]))
 
But if try to reference the slicer, then the table s not updated correctly.
 
Family = SELECTCOLUMNS(union(Adults,'Children'),"Name",[Name],"Age",IF([Name]="Bob",'Adults'[mAge],[Age]))
 
I suspect it's something to do with context but it's pretty urgent that I get this or something that achieves the same result working.
 
Please help and thanks.
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous ,Press Alt+ enter in the formula box. There up ^ at the end, if you press that it will take the complete page

     

    In case your issue is resolved, please mark the solution.

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for replying.

       

      The tables are straight-forward.

      Adults.  Two columns Name and Age.

      Bob | 50

      Sue | 35

      Children. Two columns Name and Age.

      Andy | 15

      Mary | 10

       

      The code creates the Family table correctly.

      Bob | 50

      Sue | 35

      Andy | 15

      Mary | 10

       

      The issue is that Bob's age has not been updated with the value of the slicer defined.  As I say I suspect it's something to do with context but the example seems very trivial and I would have thought there would be some way of achieving the same result.

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous ,

        I tried something like this.

        Table = union(SELECTCOLUMNS(Adult,"Name",Adult[Name],"Age",if(Adult[Name]="Bob",75,Adult[Age])),Child)