Count distinct values based on single column criteria

[athgeorge]

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!

[Anonymous · Accepted 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.  

[athgeorge]

This actually worked, thank you very much!

Now the only thing I need to implement is date range.

[Anonymous]

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. 

[athgeorge]

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!