Distinctcount for values from multiple colums

[vvibhakar]

Hello,

I am trying to analyse all the sourcing projects. We have 10 different columns for each Vendor and its price. I want to have a measure which shows me distinct count of all the vendors combined. Same vendors could appear in multiple columns due to multiple projects. Please see below the type of data I have. 

So the result should be, 15 distinct Vendors for below example. Any suggestions?

Thanks.

Project No.	Vendor 1	Vendor 1 price	Vendor 2	Vendor 2 price	Vendor 3	Vendor 3 price
1	ABC	97	LMN	66	DEF	15
2	XYZ	24	ABC	57	FLS	95
3	PQRS	26	STO	66	JWB	79
4	LMN	65	FRA	90	FSV	39
5	DEF	64	GFR	55	ABC	40
6	HIJ	80	PQRS	60	LWC	72
7	ZAC	46	DEF	61	PQRS	28

[sqlguru448]

please try below

= DISTINCTCOUNT(Vendor1) +  DISTINCTCOUNT(Vendor2) +  DISTINCTCOUNT(Vendor3)

[vvibhakar]

I had tried already, but it adds up the distinct count. In the given example, the final value still shows 21.

[Zubair_Muhammad · Accepted solution]

@vvibhakar

May be a MEASURE like

Measure =
COUNTROWS (
    DISTINCT (
        UNION (
            VALUES ( TableName[Vendor 1] ),
            VALUES ( TableName[Vendor 2] ),
            VALUES ( TableName[Vendor 3] )
        )
    )
)