table with dynamic values (columns)

[giorgiokatr]

is it possible in a table to have dynamic values depending on a slicer?

Rows will have Branch

and values will have different columns each time i select a value from a slicer

(eg sales when i select sales etc)

each value is a different column on my database

[Hrishid]

I need some expertise here.

I have imported excel from sharepoint in power bi as dataset.

In power bi desktop i have connected it as live dataset using get data method.

Now the issue is that i want to create the dynamic axis using modelling》parameter》field parameter. But it seems that the parameter is greyedout.

Any expert please suggest how can i create dynamic axis [field headers]

[Baskar]

Cool,

1. We can't change the calculated column value while selecting in slicer.

but we can achieve whatever u looking for with the help of Calculated measure. please share your fromula .

[giorgiokatr]

i am sharing my pbix to help you understand if it is possible!

https://drive.google.com/file/d/0Bw-_YmJ3Q49gTzVKUjdhUEZUQ0U/view?usp=sharing

[v-shex-msft]

Hi @giorgiokatr,

You can refer to below method to show the dynamic fields value.

Add a index column to current table.(let measure calculate all the rows)

 

Add a measure to slicer's source table to get the selected value.

Selected = if(HASONEVALUE(selection[Slicer]),VALUES(selection[Slicer]),BLANK())

Create the table visual.

Result:

Notice: if table not contain the index column, it will only show one value.

Regards,

Xiaoxin Sheng

[giorgiokatr]

thanks @v-shex-msft! what is the dax formula for field1 and field2?

[v-shex-msft · Accepted solution]

Hi @giorgiokatr,

Sorry, It seems like I have forgot to paste the measures.

field1 =SWITCH([Selected],"Sales",Max(Table[Col1]),"Other",Max(Table[Col3]))
field2 =SWITCH([Selected],"Sales",Max(Table[Col2]),"Other",Max(Table[Col4]))

Regards,

Xiaoxin Sheng

[Swamy3105]

Hi v-shex-msft,

it's a great solution, i was strugling to get the column values based on the slicer selection from another table.

thanks for the solution.

Regards,

Swamy

[YourDataOnDrugs]

Is it possible to do this when your measures use different formatting?

I have three measures that my users need to be able to swap out in the same field.  This worked for the first two, which are the same format (whole number), but the third value is a percentage.  Using this process I've only been able to keep all in the same format (whole number or percent for all).

[ramodNPK]

hi @v-shex-msft

Could be please send the pbix file with multiple rows in the input table.

I am not able to see the exact output, I see the max value of the list of values in the columns as the DAX epression says(Max in it).

Thanks in advance.

Regards,

Pramod Kumar

[giorgiokatr]

thanks @v-shex-msft great and clever solution!

[giorgiokatr]

and my only remaing problem @v-shex-msft is how to name dynamic the field1 and field2..... 

because noone will know that field1 has the values of column1

[BetterCallFrank]

Hi @giorgiokatr

look at my previous answer at https://community.powerbi.com/t5/Desktop/Changing-column-graph-displayed-value/m-p/108427/highlight/... - this is a good pattern to make it work like you want 🙂

HTH,

Frank

[giorgiokatr]

thanks @BetterCallFrank!

switch i a good solution but i would like to have 5 values for example not only one depending on slicer selection.

example when i select sales from slicer to have in a table my 5 sales columns (phone sales, laptop sales eg)

when i select profit to have my other 5 profit columns...

[CheenuSing]

Hi @giorgiokatr

Can you share some data and the visual you expect to understand the problem better.  You can do this on onedrive and post the link

Cheers

CheenuSing