Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Card from 2 separate data

Can you guys help me with my problem?

I have 2 separate data/table with names.

The reason why we have to separate data is because they have a separate line of business.

However, these tables can have the same name input and can repeat.

What I want is a Card that would count all the names. However, the repeating names will only count as 1.

Here's a visualization of the data:

 

Name 1Name 2
NoahJulia
JeraldMonroe
AugustNoah
JeraldChristian
ChristianKurt

 

Given that, I want a card that would show as 7 since other names are repeating.

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    Anonymous 

     

    Please try below measure

    measure3 = 
VAR namelist =
    VALUES ( 'Table'[Name1] )
var namelist2=VALUES('Table (2)'[Name2])
    var newtable= distinct(UNION(namelist,namelist2))
return COUNTROWS(newtable)

    Hope this is helpful.

     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Anonymous 

     

    Please try below measure

    measure3 = 
VAR namelist =
    VALUES ( 'Table'[Name1] )
var namelist2=VALUES('Table (2)'[Name2])
    var newtable= distinct(UNION(namelist,namelist2))
return COUNTROWS(newtable)

    Hope this is helpful.

     

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Anonymous 

     

    I am not sure why you deleted your latest update. Maybe it was by mistake.

     

    Exclude "Noah"

    Measure 2 = 
VAR namelist =
    CALCULATETABLE(VALUES ( 'Table1'[Name 1] ),FILTER(Table1,Table1[Name 1]<>"Noah"))
var namelist2=
    CALCULATETABLE(VALUES('Table2'[Name 2]),FILTER(Table2,Table2[Name 2]<>"Noah"))
var newtable= 
    DISTINCT(UNION(namelist,namelist2))
return COUNTROWS(newtable)

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try like

    countx(distinct(union(Table[Name 1], Table[Name 2])),[Name 1])