Forum Discussion

Rakesk13's avatar
Rakesk13
Helper III
3 months ago
Solved

How to get count from two different table

Hi team,

 

I need to Count  Emp Detail + Emp Summ i.e 8 + 5 =  13

 

I have data like this : 

 

EMP Details

 

 

EMP ID 

EMP NAME 

AGE

  AMT

1

A

1

1000

2

B

2

45

3

C

3

2000

4

D

6

3444

6

F

7

1232

12

CC

12

67

23

GG

34

666

56

A

44

5555

11

A

12

1233

15

A

14

3434

34

C

16

5652

36

D

56

1333

41

F

11

3444

 

Emp Summ:

EMP ID 

EMP NAME

AGE

AMT

5

E

4

2333

6

F

2

45

1

A

23

67

11

AA

18

45

12

BB

13

45

13

CC

31

7777

14

DD

80

5

15

FF

11

2

17

F

23

89

18

CC

1

1200

 

For Emp Details i have created calculation as:

 

Un match_Details = COUNTROWS(EXCEPT(DISTINCT('EMP Details'[EMP ID_DE]),DISTINCT('Sheet1 (2)'[EMP ID])))

 

 

 

 

Same Calculation are for Emp Summ as follows:

 

Un match_Summ = COUNTROWS(EXCEPT(DISTINCT('Sheet1 (2)'[EMP ID]),DISTINCT('EMP Details'[EMP ID_DE])))

 

I need to Count  Emp Detail + Emp Summ i.e 8 + 5 =  13.

How to get count from two different.

 

Thanks & Regards,

Rakesh Jadhav

 

Need Help
  • jgeddes's avatar
    jgeddes
    3 months ago

    Here is one way to do this...

    Measure = 
var _distDet = 
DISTINCT(EMP_Details[EMP ID ])                  //unique Details Ids
var _distSumm = 
DISTINCT(EMP_Summ[EMP ID])                      //unique Summary Ids
var _commonIDs =
INTERSECT(_distDet, _distSumm)                  //unique Ids found in both tables
var _unqDet = 
COUNTROWS(_distDet) - COUNTROWS(_commonIDs)     //count of unique Details Ids not in common table
var _unqSumm =
COUNTROWS(_distSumm) - COUNTROWS(_commonIDs)    //count of unique Summary Ids not in common table
RETURN
_unqDet + _unqSumm

5 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 months ago

    Here is one way to do this...

    Measure = 
var _distDet = 
DISTINCT(EMP_Details[EMP ID ])                  //unique Details Ids
var _distSumm = 
DISTINCT(EMP_Summ[EMP ID])                      //unique Summary Ids
var _commonIDs =
INTERSECT(_distDet, _distSumm)                  //unique Ids found in both tables
var _unqDet = 
COUNTROWS(_distDet) - COUNTROWS(_commonIDs)     //count of unique Details Ids not in common table
var _unqSumm =
COUNTROWS(_distSumm) - COUNTROWS(_commonIDs)    //count of unique Summary Ids not in common table
RETURN
_unqDet + _unqSumm
  • Natarajan_M's avatar
    Natarajan_M
    Super User
    3 months ago

    Hi Rakesk13 , Can you explain what you meant by this logic 
    I need to Count  Emp Detail + Emp Summ i.e 8 + 5 =  13
    8 corresponds to distinct values of emp id in details table ? or distinct values of emp id in details that is not in summary ?

    if you can explain the logic with the sample data it will be great 

    Thanks!

    Natarajan Manivasagan

    If you found this helpful, please consider giving it a Kudos and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

     

    🏆 Best Solution for Enterprise BI — 2026 Microsoft Fabric Semantic Link Developer Experience Challenge
    👉 Microsoft announcement · View the winning notebook

     

    For more Power BI tips and discussions, let's connect on LinkedIn.

     

    Cheers!

  • Lodha_Jaydeep's avatar
    Lodha_Jaydeep
    Solution Sage
    3 months ago

    Hi Rakesk13,

     

    Thanks for reaching, you can do something like,

     

    Total Count =
VAR MatchInDetails =
    COUNTROWS (
        FILTER (
            'Emp Details',
            'Emp Details'[EMP ID] IN VALUES ( 'Emp Sum'[EMP ID] )
        )
    )
VAR DetailOnly =
    EXCEPT (
        DISTINCT ( 'EMP Details'[EMP ID] ),
        DISTINCT ( 'Emp Sum'[EMP ID] )
    )
VAR SummOnly =
    EXCEPT (
        DISTINCT ( 'Emp Sum'[EMP ID] ),
        DISTINCT ( 'EMP Details'[EMP ID] )
    )
VAR UnmatchedDistinct =
    COUNTROWS ( UNION ( DetailOnly, SummOnly ) )
RETURN
    MatchInDetails + UnmatchedDistinct

    this will give the expected output as you want

    To verify

    Match in Emp Details = 
COUNTROWS (
    FILTER (
        'Emp Details',
        'Emp Details'[EMP ID] IN VALUES ( 'Emp Sum'[EMP ID] )
    )
)

Total Unmatched Count = 
VAR DetailOnly =
    EXCEPT (
        DISTINCT ( 'EMP Details'[EMP ID] ),
        DISTINCT ( 'Emp Sum'[EMP ID] )
    )
VAR SummOnly =
    EXCEPT (
        DISTINCT ( 'Emp Sum'[EMP ID] ),
        DISTINCT ( 'EMP Details'[EMP ID] )
    )
VAR Combined =
    UNION ( DetailOnly, SummOnly )
RETURN
    COUNTROWS ( Combined )


    Hope this is what you need. Please consider as an accepted solution if help or give some kudos.

  • Ray_Minds's avatar
    Ray_Minds
    Solution Supplier
    3 months ago

    Solution:
    You can simply create a the below measure to get that result.

    Measure:

    Total_Unmatched = COUNTROWS(EXCEPT(DISTINCT('EMP Details'[EMP ID]),DISTINCT('Emp Summ'[EMP ID]))) + COUNTROWS(EXCEPT(DISTINCT('Emp Summ'[EMP ID]),DISTINCT('EMP Details'[EMP ID])))

    Result: