Forum Discussion
Dynamic column value based on MEASURE (not Slicer)
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
2 Replies
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
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
