Forum Discussion

stribor45's avatar
stribor45
Post Prodigy
1 year ago
Solved

Unique values in each table

I am trying to find unique values in each tables. So for example get unique id's in table A and do the same for table B. Now I want to make sure when I count these ID's not to count them twice since  same ids can be in each table. I know what I need to use base table for this code but cant sedem to understand how to resolve this. Can anyone assist please 

 

DEFINE 

VAR A = VALUES('Table A'[ID])
VAR B = VALUES('Table B'[ID])
VAR C = UNION(A,B)
VAR D = DISTINCTCOUNT(C[ID])

EVALUATE 
D
  • Irwan's avatar
    Irwan
    1 year ago

    hello stribor45 

     

    please check if this accomodate your need.

    i made a simple dataset.

    based on above dataset, the distinct count should be 11.

     

    i assumed you want to do this in measure, so create a new measure with following DAX.

     

    Hope this will help.

    Thank you.

    stribor45 - 21Apr25.pbix19 KB

4 Replies

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello stribor45 

     

    please check if this accomodate your need.

    i made a simple dataset.

    based on above dataset, the distinct count should be 11.

     

    i assumed you want to do this in measure, so create a new measure with following DAX.

     

    Hope this will help.

    Thank you.

    stribor45 - 21Apr25.pbix19 KB
  • stribor45's avatar
    stribor45
    Post Prodigy
    1 year ago

    This works but how does the outer DISTINCT know that we are talkinjg about ID column? Is it because it is only one column?

    • Irwan's avatar
      Irwan
      Super User
      1 year ago

      hello stribor45 

       

      i might be misunderstood but you can target the value you want to calculate the distinct value.

       

      otherwise, please share your sample dataset and your expected outcome.

       

      Thank you.

  • v-hashadapu's avatar
    v-hashadapu
    Community Support
    1 year ago

    Hi stribor45 , Thank you for reaching out to the Microsoft Community Forum.

    Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
    Thank you.