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

[Koss]

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"

ID	Pool	Business 1	Business 2	Business 3	Business 4
18	A	ABC	BCD	CDE	DEF
1	B	BCD	EFG	GHI	ABC
18	C	EFG	ABC	CDE	HIJ
2	A	ABC	BCD	CDE	DEF
33	C	EFG	ABC	CDE	HIJ
1	A	ABC	BCD	CDE	DEF
2	D	XYZ	CDE	EFG	DEF
2	C	EFG	ABC	CDE	HIJ

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 code	Business name
ABC	Amazing business
BCD	Cool business
CDE	Awesome Business
DEF	Not so bad business
EFG	Horrible business
GHI	not even a business
HIJ	Stardard business
XYZ	Astonishing 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

[v-yulgu-msft · Accepted solution]

Hi @Koss,

Please unpivot 'tab1' to convert to below structure.

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

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

Best regards,

Yuliana Gu