Forum Discussion

Rakesk13's avatar
Rakesk13
Helper III
2 months ago
Solved

How to get Count from two different table- Un-match Count Table Record with New Table

Hi team,

 

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 Summary Table  i have created calculation as(Which will give Un-match values from EMP Details Table)

 

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

 

 

 

 

EMP ID

EMP NAME

Age

Amt

Un-Matched Values

5

E

4

2333

#N/A

11

AA

18

45

#N/A

12

BB

13

45

#N/A

14

DD

80

5

#N/A

15

FF

11

2

#N/A

 

 

Its giving Count of 5 which is Un-match values from EMP Details Table(This Un-Matched Values  Columns #NA I have performed manually in excel)

 

I do have Another Table

 

EMP_ADD as:

 

EMP NAME

EMP ADDRESS

E

XX1X

BB

ERE3R

FF

JHJ8Q

 

 

 

 

 

 

 

Now how to perform calculation in same sheet1(2) table to get match record between 5(Un-Matched Values) record and EMP_ADD table

 

Output Should be like this:

 

EMP ID

EMP NAME

Age

Amt

Un-Matched Values

 

5

E

4

2333

#N/A

E

12

BB

13

45

#N/A

BB

15

FF

11

2

#N/A

FF

Thanks & Regards,

Rakesh Jadhav

 

 

 

 

 

 

Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    2 months ago

    Hi Rakesk13, 
    Thank you for the screenshot and follow-up question.

    The error occurs because the DAX was converted into a measure. Measures do not have row context, so Power BI cannot determine a single EMP ID value and returns the "A single value for column cannot be determined" error. 

    In this scenario, use a calculated column instead of a measure. Create a column that first identifies unmatched EMP IDs and then uses LOOKUPVALUE (or RELATED if a relationship exists) to return the matching EMP NAME from EMP_ADD. After that, filter the visual to show only rows where the returned value is not blank. This will return the expected records E, BB, and FF.

    Create this column in Sheet1 (2):

    Matched_Name =
VAR IsUnmatched =
    NOT (
        'Sheet1 (2)'[EMP ID]
            IN VALUES ( 'EMP Details'[EMP ID_DE] )
    )
RETURN
IF (
    IsUnmatched,
    LOOKUPVALUE (
        EMP_ADD[EMP NAME],
        EMP_ADD[EMP NAME], 'Sheet1 (2)'[EMP NAME]
    ),
    BLANK()
)

    Then add: 

    Show_Record =
IF (
    NOT ISBLANK ( 'Sheet1 (2)'[Matched_Name] ),
    1,
    0
)

    Filter the visual:

    Show_Record = 1

     

    Hope this helps. If you have any questions regarding this, please feel free to reach out us. We will be happy to help.

     

7 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    2 months ago

    Hi Rakesk13,

     

    Try below DAX, Store the table in a variable first and then apply lookupvalue to fetch against those unmatched rows.

     

    Matched_Name =
    VAR IsUnmatched =
    NOT (
    'Sheet1 (2)'[EMP ID]
    IN VALUES ( 'EMP Details'[EMP ID_DE] )
    )
    RETURN
    IF (
    IsUnmatched,
    LOOKUPVALUE (
    EMP_ADD[EMP NAME],
    EMP_ADD[EMP NAME], 'Sheet1 (2)'[EMP NAME]
    )
    )

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

    • Rakesk13's avatar
      Rakesk13
      Helper III
      2 months ago

      Thanks for Quick Response.

      But Facing Count issue only record E we are getting, we need E,BB FF record in ours output.

       

       

      we are looking for below output :

       

       

      EMP ID

      EMP NAME

      Age

      Amt

      Un-Matched Values

       

      5

      E

      4

      2333

      #N/A

      E

      12

      BB

      13

      45

      #N/A

      BB

      15

      FF

      11

      2

      #N/A

      FF

       

      Please can you help on this .

       

      Thanks & Regards,

      Rakesh Jadhav

      • grazitti_sapna's avatar
        grazitti_sapna
        Super User
        2 months ago

        Hi Rakesk13,

         

        I can see that you have created a calculated column and not a measure, try creating a measure and then test the values