Forum Discussion
DAX query to create new boolean columns
Hello,
I need help with the following DAX query. I am using Power Bi Desktop. I have a table_1 with the following fields
record_ID | text |
record_1 | 'blablabla' |
record_2 | 'blablabla2' |
where record_ID is the primary key (unique values) and a table_2 with different tags related to the records in table_1
| record_ID | tag_ID |
| record_1 | tag1 |
| record_1 | tag2 |
record_2 | tag1 |
record_2 | tag3 |
and a third table with the name related to the tag
| tag_ID | tag_name |
| tag1 | name_tag1 |
| tag2 | name_tag2 |
| tag3 | name_tag3 |
Given that I have n different tags in total (3 in the example), I would like to
- add to table_1 n different boolean columns with true/false values if the related record_id has that tag or not;
- add a final column with all the tags concatenated and comma-separated.
In this example, the result would be:
record_ID | text | name_tag1 | name_tag2 | name_tag3 | all_tags |
record_1 | 'blablabla' | True | True | False | name_tag1, name_tag2 |
record_2 | 'blablabla2' | True | False | True | name_tag1, name_tag3 |
how can I achieve this using DAX?
HiAnonymous
You are looking for a sort of unpivot function to create colunms using DAX. You can do this in m-query, but as far as I know you can't dynamically create columns in dax without some sort of manual intervention.
Using a measure it is no problem to create your table.hope this still a bit helpfull.
Jan
4 Replies
HiAnonymous
You are looking for a sort of unpivot function to create colunms using DAX. You can do this in m-query, but as far as I know you can't dynamically create columns in dax without some sort of manual intervention.
Using a measure it is no problem to create your table.hope this still a bit helpfull.
Jan
Hi Anonymous ,
thanks for your help and sorry for the late response.
When I try your DAX formula, I get the error "Expressions that yield variant data-type cannot be used to define calculated columns." if I create a new column with your expression. If, as you suggest, I create a measure, then I am able to use it in a visual, but only the "Total" column is created, not the boolean columns Tag_T1, Tag_T2 etc.
I have a further question: is creating a measure, rather than defining a new table in the data model, the most efficient way to do it? Wouldn't a new table allow me to more flexibility when creating new visuals based on this data? I am asking since I am completely new to power bi. Thank you.