Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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?
Solved! Go to 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 .
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
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.
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
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.
User | Count |
---|---|
53 | |
35 | |
20 | |
16 | |
15 |
User | Count |
---|---|
94 | |
76 | |
36 | |
22 | |
18 |