Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
May be a MEASURE like
Measure = COUNTROWS ( DISTINCT ( UNION ( VALUES ( TableName[Vendor 1] ), VALUES ( TableName[Vendor 2] ), VALUES ( TableName[Vendor 3] ) ) ) )
please try below
= DISTINCTCOUNT(Vendor1) + DISTINCTCOUNT(Vendor2) + DISTINCTCOUNT(Vendor3)
I had tried already, but it adds up the distinct count. In the given example, the final value still shows 21.
May be a MEASURE like
Measure = COUNTROWS ( DISTINCT ( UNION ( VALUES ( TableName[Vendor 1] ), VALUES ( TableName[Vendor 2] ), VALUES ( TableName[Vendor 3] ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |