Dynamic column value based on MEASURE (not Slicer)

[bodaypal]

 Hi there,

I am looking for a solution for the following problem.

I would like to create a new column to a table that takes its values as a condition based on a measure. I know, I can be solve it with slicer by unpivoting all dimensions to one table but my source table should be refreshed quite often and I do not want to make this unpivot stuff every time.

So the example, I have a table named e.g. EMPLOYEES:

ID	GENDER	AGE_GOURP	EDUCATION	NO_PEOPLE
1	M	20-30	primary	12
2	F	30-40	secondary	23
3	M	30-40	primary	12
4	F	20-30	tertiary	24
5	F	40-50	secondary	32
6	M	40-50	primary	21
7	M	20-30	tertiary	32
8	F	30-40	secondary	12
9	F	20-30	tertiary	32
10	F	40-50	primary	14

I have an other table named Dimensions

DIMENSIONS

GENDER

AGE_GOURP

EDUCATION

I can create a measure FILTEREDED_DIM that takes its values by selecting (filtering) an element from 'Dimensions'[DIMENSIONS] 

let's say

FILTERED_DIM = if(
ISCROSSFILTERED('Dimensions'[DIMENSIONS]);
FIRSTNONBLANK('Dimensions'[DIMENSIONS];
""))

I would like to create a new column to the table EMPLOYEES e.g. CHOSEN_DIM that takes its values based on the value of the measure FILTERED_DIM

something like this: 

CHOSEN_DIM = IF(
[FILTERED_DIM]="AGE_GROUP";'Employees'[AGE_GOURP];
     if([FILTEREDED_DIM]="EDUCATION";'Employees'[EDUCATION];
     'Employees'[GENDER]
     )

)

It results always as false...

Do you have any idea, work arround? Or even the idea is not suited for PBI, that is why I did not find any solution already. If so, any idea on how to recreate automatically my unpivoted table.

Thanks

Pal

[v-yuta-msft]

Hi bodaypal,

Firstly, your requirement seems like to achieve a dynamic calculate column based on measure(or slicer), right? Unfortunately, power no doesn't support dynamic calculate column. You should use measure instead of calculate column.

Secondly, there's no relationship between ‘DIMENSIONS’ and 'EMPLOYEES', you should create a column in both table so that you can build a relationship between two tables. Then you can use measure in 'DIMENSIONS' to filter 'EMPLOYEES'.

Regards，

Jimmy Tao

[bodaypal]

Dear Jimmy Tao,

Thanks for your reply. I might not be clear.

My original problem was to choose a column dynamically. Following my example I wanted to create a bar chart of sum(PEOPLE_NO) by GENDER than choose the dimension (AGE_GROUP) from a list or so and than my visual would turn to sum(PEOLPLE_NO) by AGE_GROUP.

Well I know a solution for that by creating a table (by unpivoting the original) with 3 columns 1. ID, 2. all dimensions, and 3. all dimension vaules. With that you can make the relations and it works nicely. It is explained e.g. here better 🙂 http://www.leanx.eu/tutorials/dynamic-dimensions-in-power-bi

BUT I wanted something easier, less tables, less data, less preparation. Following my example. I wanted to create a column based on the value of the measure FILTERED_DIM. If I chose "AGE_GROUP" some calculation would create a new column [CHOSEN_DIM] with the values of [AGE_GROUP]. Then I can use this column in my visuals.

 You advised me to use a measure instead of column, but I think for this I need a column not a measure.

ID	GENDER	AGE_GOURP	EDUCATION	NO_PEOPLE	CHOSEN_DIM
1	M	20-30	primary	12	20-30
2	F	30-40	secondary	23	30-40
3	M	30-40	primary	12	30-40
4	F	20-30	tertiary	24	20-30
5	F	40-50	secondary	32	40-50
6	M	40-50	primary	21	40-50
7	M	20-30	tertiary	32	20-30
8	F	30-40	secondary	12	30-40
9	F	20-30	tertiary	32	20-30
10	F	40-50	primary	14	40-50

So, if I understood correctly, this is what, that is not possible in PBI.

By the way I put an other question at the end „…any idea on how to recreate automatically my unpivoted table” and I find a solution by myself. If I create the unpivoted tables in query editor then in refresh it refreshes all the tables.

thanks again

Pal