Forum Discussion

ValerienSegard's avatar
ValerienSegard
Frequent Visitor
5 years ago
Solved

Disctinct & count in two columns

Hello 

 

How are you ? 

 

I just spent the day on this simple request : i want to be able to built a graph with the name of the sponsors and how many time they contributes to a project. 

 

Input: (3 columns)

Project Name / Sponsor 1 / Sponsor 2

Project A / Jean PIPO / Michel BONJOUR

Project B / Michel BONJOUR / null

Project C / Jean PIPO / null

Project D / null / Michel BONJOUR 

 

Outcome : 

Jean PIPO contributes 2 times

Michel BONJOUR contributes 3 times.

 

Can you help me ? A big thank you in advance

Need Help
  • negi007's avatar
    negi007
    5 years ago

    ValerienSegard  in that case

     

    Step 1: create a new table using below code

    Sponser_Count_tab = DISTINCT(UNION(VALUES('Table'[ Sponsor 1 ]),VALUES('Table'[ Sponsor 2])))
    this will create a table of unique values from two columns
     
    Step 2: create relationship between new table and existing tables
     
     

     

     

    Step 3: Since you can have only one active relationship between two tables, you will have to use both active and inactive relationship to calculate sum
     
    Count_Project = CALCULATE(COUNT('Table'[ Sponsor 1 ]))+CALCULATE(COUNT('Table'[ Sponsor 2]),USERELATIONSHIP(Sponser_Count_tab[ Sponsor 1 ],'Table'[ Sponsor 2]))
     
    Final output is below
     

     i hope you would like this solution

3 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    ValerienSegard  in that case

     

    Step 1: create a new table using below code

    Sponser_Count_tab = DISTINCT(UNION(VALUES('Table'[ Sponsor 1 ]),VALUES('Table'[ Sponsor 2])))
    this will create a table of unique values from two columns
     
    Step 2: create relationship between new table and existing tables
     
     

     

     

    Step 3: Since you can have only one active relationship between two tables, you will have to use both active and inactive relationship to calculate sum
     
    Count_Project = CALCULATE(COUNT('Table'[ Sponsor 1 ]))+CALCULATE(COUNT('Table'[ Sponsor 2]),USERELATIONSHIP(Sponser_Count_tab[ Sponsor 1 ],'Table'[ Sponsor 2]))
     
    Final output is below
     

     i hope you would like this solution

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    ValerienSegard in this case i suggest you to combine values for column 2 and column 3 in one column in powerquery like below using unpviot columns feature. You select your columns and then Unpivot columns option.

     

     

    Your data would like below after doing unpivot columns

     

     

    then you can use group by to count values

     

    your final output is like below 

     

     

     

    Thanks

     

    • ValerienSegard's avatar
      ValerienSegard
      Frequent Visitor
      5 years ago

      Thank you for your answer. But i'm trying to create a virtual_table : 

      vTable = Union(values('Portfolio'[Sponsor 1]),VALUES('Portfolio'[Sponsor 2]))

       
      And apply a COUNT
       
      Solution which could be suitable with filter/slicers...