Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone, bear with me because Im not sure how to present my issue :).
I am trying to recreate a similar report that I have in Data studio to Power BI
My main issue is the treemap graph bellow. The google sheet I am using for that graph has a singe column Interests targeting, with all the values separeted with comma. So I ve created a new dimension that uses the bellow regexp and counts distinct all the interests and groups them under their respective pillar.
when (REGEXP_MATCH(Interest targeting,".*Performing arts.*")) then "Performing arts"
Now in power bi, I've used the split column and created multiple interest targeting columns as shown bellow.
My issue is, I cant find a way to match all the interests to their repsective pillar.
Thank you!
Solved! Go to Solution.
Hi @athgeorge,
According to your description, you want to count distinct values based on single column criteria ,right? Here are my steps you can follow as a solution.
(1)Here are my test data.
(2)The operation of selecting the first column and reversing the other columns.
(3)Delete the second column [Attribute].
(4)Filter on the [Values] column.
(5)Create a table.
Table 2 = VALUES('Table'[Value])
The table relationship is shown in the following figure.
(6)Create a visual as shown in the following image.
(7)Then the result is as follows.
If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @athgeorge,
According to your description, you want to count distinct values based on single column criteria ,right? Here are my steps you can follow as a solution.
(1)Here are my test data.
(2)The operation of selecting the first column and reversing the other columns.
(3)Delete the second column [Attribute].
(4)Filter on the [Values] column.
(5)Create a table.
Table 2 = VALUES('Table'[Value])
The table relationship is shown in the following figure.
(6)Create a visual as shown in the following image.
(7)Then the result is as follows.
If this method does not meet your needs, you can provide us with detailed input and output examples in tabular form so that we can better solve the problem for you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This actually worked, thank you very much!
Now the only thing I need to implement is date range.
Hi @athgeorge ,
What kind of result do you want to match for this regular expression? I didn't find which column the interest targeting matched in the data below.
Please provide us with detailed input and output examples in tabular form so that we can better solve the problem for you. Please take care to protect your private data.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Neeko,
Thanks for the reply.
My data source is in tabular format, so Im gonna try and give you an exaple table bellow:
Pillars | Interst targeting.1 | Interst targeting.2 | Interst targeting.3 | Interst targeting.4 | Interst targeting.5 |
Pillar1 | Interest1 | Interest2 | Interest3 | Interest4 | null |
Pillar2 | Interest1 | Interest2 | Interest3 | Interest5 | Interest5 |
Pillar1 | Interest1 | Interest4 | Interest5 | null | null |
Pillar3 | Interest1 | Interest4 | Interest4 | Interest1 | null |
Pillar3 | Interest1 | Interest2 | Interest4 | null | null |
The data i want to extract and show in a graph is, the sum of all the unique interests per pillar.
Eg.
Pillar1
2xInterst1
1xInterest2
1xInterest3
2xInterests4
1xInterest5
Pillar3
3xInterst1
1xInterest2
3xInterests4
So, I am trying to find a function that can count distinct all the interests from all the Interest targeting columns to each respective Pillar from multiple rows (of the Pillar column).
I am sorry if this still doesn't make sense, English is not my primary language 😕
Thank you!
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |