Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
athgeorge
New Member

Count distinct values based on single column criteria

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"

 

Capture1.PNG

 

 

Now in power bi, I've used the split column and created multiple interest targeting columns as shown bellow.

Capture.PNG

 

 

 

 

My issue is, I cant find a way to match all the interests to their repsective pillar.

 

Thank you!

 

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1663323943632.png

(2)The operation of selecting the first column and reversing the other columns.

vtangjiemsft_1-1663323943648.png

 

 vtangjiemsft_2-1663323943663.png

(3)Delete the second column [Attribute].

vtangjiemsft_9-1663324125680.png

 

(4)Filter on the [Values] column.

vtangjiemsft_10-1663324137274.png

vtangjiemsft_11-1663324152915.png

 

(5)Create a table.

Table 2 = VALUES('Table'[Value])

The table relationship is shown in the following figure.

vtangjiemsft_6-1663323943675.png

(6)Create a visual as shown in the following image.

vtangjiemsft_7-1663323943679.png

(7)Then the result is as follows.  

vtangjiemsft_8-1663323943681.png

 

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 posthelps, then please considerAccept it as the solutionto help the other members find it more quickly. 

 

View solution in original post

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1663323943632.png

(2)The operation of selecting the first column and reversing the other columns.

vtangjiemsft_1-1663323943648.png

 

 vtangjiemsft_2-1663323943663.png

(3)Delete the second column [Attribute].

vtangjiemsft_9-1663324125680.png

 

(4)Filter on the [Values] column.

vtangjiemsft_10-1663324137274.png

vtangjiemsft_11-1663324152915.png

 

(5)Create a table.

Table 2 = VALUES('Table'[Value])

The table relationship is shown in the following figure.

vtangjiemsft_6-1663323943675.png

(6)Create a visual as shown in the following image.

vtangjiemsft_7-1663323943679.png

(7)Then the result is as follows.  

vtangjiemsft_8-1663323943681.png

 

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 posthelps, then please considerAccept it as the solutionto 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.

v-tangjie-msft
Community Support
Community Support

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 posthelps, then please considerAccept it as the solutionto 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.1Interst targeting.2Interst targeting.3Interst targeting.4Interst targeting.5
Pillar1Interest1

Interest2

Interest3Interest4null
Pillar2Interest1Interest2Interest3Interest5Interest5

Pillar1

Interest1

Interest4Interest5nullnull
Pillar3Interest1Interest4Interest4Interest1null
Pillar3Interest1Interest2Interest4nullnull

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors