Using a Measure in SELECTCOLUMNS

[Anonymous]

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.

[amitchandak]

@Anonymous 

Can you share sample data and sample output.

[Anonymous]

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]

@Anonymous ,

I tried something like this.

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

[Anonymous]

OK, I can see you've swapped the UNION statement and the SELECTCOLUMNS round.

The main issue though is setting the age of Bob to be the value stored in the measure mAge.

So, to reiterate, if you create a 'What-If' parameter called pAge with a slider.  Create a measure called mAge and set it as follows

mAge = pAge[pAge Value].

What I want is for the combined table to show Bob with the age set in the slider.

For me, this is not working as Bob's age stays the same.

Thanks for your suggestions.

[amitchandak]

Check if this can help. I create a new measure in merged table

[Anonymous]

That looks like exactly what I am after.

One more simple question and it's a really simple one, I suspect.

I am using PBI Desktop.

If I select 'New Measure', for example, I get a single line area in which I can type my code.  How do I expand this area so that it is larger.  Simple question I know but I just can't see how to do this.

Many thanks for all your help, your solution will really help me.

[amitchandak · Accepted solution]

@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.