Dynamically Create Column Using Slicer

[vgk_17]

Hi,

I have 2 tables - X (contains Name and Address columns) and Y (Contains Attribute column) as follows. I am feeding Attribute as slicer. I need to dynamically create in X table based on slicer selection.

Name	Address
A	1
B	2
C	3

Attribute

Name_Attribute

Address_Attribute

When I select Name_Arttribute in slicer, then I should get table X with additional column New_Col which should have its values copied from Name column of the table X. And When I select Address_Arttribute in slicer, then I should get table X with additional column New_Col which should have its values copied from Address column of the table X

When Slicer selection - Name_Attribute, then table X

Name	Address	New_Col
A	1	A
B	2	B
C	3	C

When Slicer selection - Address_Attribute, then table X

Name	Address	New_Col
A	1	1
B	2	2
C	3	3

I am using following DAX, but it is not working - 

New_Column = Switch(

selectedvalue(Attribute)),

"Name_Attribute", [Name],

"Address_Attribute",[Address]

)

And when I used following DAX, no matter what slicer selection is made, it is always picking up Address_Attribute and giving 1,2,3 in New_Col

Column = Switch(

FirstNonBlank(Attribute),1),

"Name_Attribute", [Name],

"Address_Attribute",[Address]

)

Any help here is highly appreciated.

Thanks

[vgk_17]

I would like to clarify one point here - New_Col is a column, not the measure. Hence, I am right clicking on table X and then clicking on New Column instead of New Measure. Reason is, I need to further use this column in Matrix where only columns can be supplied, not the measures.

Looping in users that I see respond often on community.

@CheenuSing @amitchandak @Greg_Deckler @parry2k 

[Greg_Deckler]

@vgk_17 - You cannot create a column that responds to user input on a slicer. The reason is that calculated columns are only calculated at the time of data refresh.

[vgk_17]

Hi @Greg_Deckler ,

Thanks for looking into it and replying. My objective here is to create a heat map using my repurposing matrix. I want Rows and Columns in that matric to be dynamic. This essentially means if we have say 10 columns in my table, I want to give user 2 slicers which user can use to select Rows and Columns of this matrix dynamically. Since I am talking about matrix, it only accepts Columns in Rows/Columns field. Hence cannot user measure. Any solution on how can I achieve this ?

Also, regarding calculated column being initialized at the time of data refresh, here is what I did to achieve this calculated column to respond on slicer selection -

1. Inserted index column in main data and created this table's duplicate

2. In the duplicate table, barring index column and the columns I want to toggle between, removed rest all. Then seleted all columns barring index and unpivoted it.

3. Made Many to 1, both relation between Main data table and duplicate data table.

4. Created another table with just 1 column and entered A and B in its column as values. This is my slicer table.

5. Created many to one, single relation between duplicate table and slicer table

6. In the duplicate table, created a calclated column with following dax-

Calculated_Column =

SWITCH(

FIRSTNONBLANK('SlicerTable'[SlicerTableColumn],TRUE()),

"A", RELATED(Main_Data[Main_Data_Col_1]),

"B", RELATED(Main_Data[Main_Data_Col_2])

)

7. Dragged Calculated_Column on canvas as a table.

8. Now when I change any value in slicer, this calculated column takes approproate value. On selecting A, it takes value from Main_Data_Col_1 and on selecting B, it takes value from Main_Data_Col_2.

I want to achieve the same in main table and not in the duplicate table, anything that you can come up / suggest ?

@CheenuSing

[Greg_Deckler]

@vgk_17 - Just so you understand that you cannot create a calculated column based upon a slicer selection. That's what it sounded like what you were doing. If you want to achieve some kind of dynamic display of information based upon a slicer selection, that is a different matter. I would suggest review the following link because I am still not exactly clear what you are trying to achieve. Pictures often help.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titl...

[vgk_17]

Hi @Greg_Deckler ,

Unfortunately i cannot provide pictures. I will try to be more clearer.

My end goal is to create Heat Map. Since this visualization is not available in PowerBI, I will use Matrix visualization and apply color gradient on its values to make it look like heat map.

I have 3 tables as listed below (no relationship exists between these 3)

Name	Address	Emp ID	Marks
A	A1	A123	10
B	A2	B123	20
C	A3	C123	30

Row Slicer

Name

Address

Emp ID

Column Slicer

Name

Address

Emp ID

When user will select Name in row slicer and Address in column slicer, then I should get below matrix-

	A1	A2	A3
A
B
C

When user will select Name in row slicer and Emp ID in column slicer, then I should get below matrix-

	A123	B123	C123
A
B
C

When user will select Address in row slicer and Name in column slicer, then I should get below matrix-

	A	B	C
A1
A2
A3

So basically, I am giving user the flexibility to be able to select which two columns he/she would like to add to the Matrix. I hope now It is clear. Any solution/idea that you could suggest. Thank you.