cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Koss Frequent Visitor
Frequent Visitor

How returning proper distinctcount value when slicing value hosted in multi columns

Hello Community,

I am struggling with the following conundrum:

 

I have a table composed with contact which are related to different businesses, let call it "tab1"

IDPoolBusiness 1Business 2 Business 3Business 4
18AABCBCDCDEDEF
1BBCDEFGGHIABC
18CEFGABCCDEHIJ
2AABCBCDCDEDEF
33CEFGABCCDEHIJ
1AABCBCDCDEDEF
2DXYZCDEEFGDEF
2CEFGABCCDEHIJ

 

As you already understand pool is related to businesses attribution

 

I have another table with business shortcode and extend name - thsi table is used, thank to relationship, as value to slice other data from other source. let's call it "tab2"

Business short codeBusiness name
ABCAmazing business
BCDCool business
CDEAwesome Business
DEFNot so bad business
EFGHorrible business
GHInot even a business
HIJStardard business
XYZAstonishing business

 

 

#1. How to build a relationship between tab1 and tab2 in order to leverage tab2 value as slicer?

#2. how to build a measure that return distinctcount after auditing column business 1 to business 4 to find the value selected in the slicer build in #1

Thanks for your help

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: How returning proper distinctcount value when slicing value hosted in multi columns

Hi @Koss,

 

Please unpivot 'tab1' to convert to below structure.

1.PNG2.PNG

 

Then, create a one to many relationship between 'tab2' and 'tab1'.

3.PNG

 

Based on my assumption, you want to calculate the distictcount for Business short code, right? Please check below screenshot.

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft v-yulgu-msft
Microsoft

Re: How returning proper distinctcount value when slicing value hosted in multi columns

Hi @Koss,

 

Please unpivot 'tab1' to convert to below structure.

1.PNG2.PNG

 

Then, create a one to many relationship between 'tab2' and 'tab1'.

3.PNG

 

Based on my assumption, you want to calculate the distictcount for Business short code, right? Please check below screenshot.

4.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors