Forum Discussion

vvibhakar's avatar
vvibhakar
Frequent Visitor
8 years ago
Solved

Distinctcount for values from multiple colums

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 1Vendor 1 priceVendor 2Vendor 2 priceVendor 3Vendor 3 price
1ABC97LMN66DEF15
2XYZ24ABC57FLS95
3PQRS26STO66JWB79
4LMN65FRA90FSV39
5DEF64GFR55ABC40
6HIJ80PQRS60LWC72
7ZAC46DEF61PQRS28
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    vvibhakar

     

    May be a MEASURE like

     

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

     

3 Replies

  • sqlguru448's avatar
    sqlguru448
    Helper III
    8 years ago

    please try below

     

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

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      vvibhakar

       

      May be a MEASURE like

       

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

       

    • vvibhakar's avatar
      vvibhakar
      Frequent Visitor
      8 years ago

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