DAX query to create new boolean columns

[Anonymous]

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

1. add to table_1 n different boolean columns with true/false values if the related record_id has that tag or not;
2. 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?

[Anonymous]

@Anonymous .

Follow  these steps.

1. Choose matrix visual.

2. Drag recordId column from table1 on Rows.

3. Drag tag_name column on Columns.

4 . Use the measure suggested by @JustJan on Values.

Thats all. 

Hope this helps.

Appreciate with kudos.

Mark as solution if this resolves your problem.

Thanks

[Anonymous]

@Anonymous 

Easier than I expected 🙂 thanks a lot, very helpful. Not sure about which reply I should mark as solution, but I guess it's the one from @JustJan since he provided the measure.

[JustJan · Accepted solution]

Hi@Anonymous 

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

[Anonymous]

Hi @JustJan ,

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.