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.    Inpu...
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

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