Change the Calculated Column values in a table in Power BI based on the slicer value

[Anonymous]

Hi All,

This post is all about changing the calculated column values in a table in power bi according to the parameter value selected by the user from a slicer.

I came across a situation in my project. I wanted to create a table visual which shows the measure values against a Calculated column named Dimension1. The  slicer contains a set of values which are the names of columns from different tables.

Values in Dimnesion1 should automatically change and show the corresponding values according to the parameter that user selects.

i.e. if user selects the parameter value-‘Reporting Segment’, the Dimension1 column in Matrix table should contain the values coming under the column Reporting Segment in another table.

I have tried to achieve this by creating a table with single column having the list of parameter values and in order to have a relation between the main table and parameter table, I cross joined the 2 tables and created a new table and that is working fine. But my main table have 7 million records and the parameter table have 15 records so while cross joining  I am getting 95 million record which is making the pbix file size 900 MB and the file is getting slower.

Could you please help me in providing any work around.

[Anonymous]

Hi @Anonymous 

In general, calculated columns are not dynamic, they are calculated at the time of data load and not again until the next data load. Need to use measures to get dynamic behavior.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Here we need a calculated column, because if the user selects Reporting Segment from the slicer, the related table visual should show the values in the column Reporting Segment from a different table.

Thanks

[Anonymous]

can you share some sample data and expected output.

[Anonymous]

Please find the tables below:

MAIN TABLE
Employee_ID	EMP_NAME	Salary	SegmentKey	Unit_key
123	ABC	$3,500	1	5
435	GHI	$3,000	3	8
678	IOP	$4,000	4	2

Segment Table
Segment_Key	Reporting Segment
1	seg1
2	seg2
3	seg3
4	seg4
5	seg5

Unit Table
Unit_Key	Reporting Unit
1	unit1
2	unit2
3	unit3
4	unit4
5	unit5

The Main Table is linked with the Segment table using Segment_Key and Unit table using Unit_Key. Our requirment is, we will have a slicer  

Reporting Segment

Reporting Unit

When we select Reporting Segment from the slicer we need a table in the visual having a column with values from column Reporting Segment. i.e our query will be like IF(SelectedValue (Slicer)="Reporting Segment", RELATED(Segment Table[Reporting Segment])). But this will not work.

[Anonymous]

So your requirement is you need to have column names in slicers and user have flexibility to select the columns in table.
Unfortunately in power BI this functionality is not possible.
You can do one thing...you can create hierarchy and add that hierarchy in table. And provide self service BI to users to select/remove column from hirrarchy in table as per their requirement

Thanks & regards,
Pravin Wattamwar