Forum Discussion
Advocate IIHow to Count from two different table Un-matched 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:
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
|
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
Hi Rakesk13,
Thank you for providing the details.
I tried reproducing the scenario using the sample data and DAX shared in the post. With the provided data, I was able to reproduce the unmatched counts, but the results don't align with the expected output (QW = 5/5 and RR = 3/1). It appears that the sample data and the expected results may not be consistent (for example, some matching EMP IDs have different Relation values across the two tables).
Could you please share a reproducible .pbix file or a correct sample dataset that exactly reflects the issue you're facing? That will help us reproduce the problem accurately and provide the appropriate solution.
Hope this helps. If you have any doubts regarding on this thread, please feel free to reach out to us. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
11 Replies
v-pnaroju-msftCommunity Support
Thankyou, parry2k for your response.
Hi Rakesk13,We appreciate your question on the Microsoft Fabric Community Forum.
Based on our understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter.
We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.Thank you
Rakesk13Hi ,Really Thanks ,its help me out.
One small help i need as in screenshot we got Only NA count as 8 which are un-match from EMP-SUMM and EMP_DETAILS table
And we got count of 3 match between EMP_SUMM and EMP_ADD table
Now I am looking for 8-3 = 5 as count.
How we can performed calculation for it.
Please can you help on this.
Thanks & Regards,
Rakesh Jadhav
- v-pnaroju-msft
Hi Rakesk13,
Based on our understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter.We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.
- Rakesk13
How to Apply Slicer filter to Un-matched records of two different table
Can you help on this please.
Hi Team,
I have data like this:
Emp Details:
EMP ID _DE
EMP Name_De
Age
amt
Relation
1
A
1
1000
QW
2
B
2
45
QW
3
C
3
2000
QW
4
D
6
3444
QW
6
F
7
1652
QW
12
CC
12
67
RR
23
GG
34
6666
QW
56
A
44
3333
RR
11
A
12
5555
RR
15
A
14
6666
QW
34
C
16
3555
RR
36
D
56
1333
RR
41
F
11
3444
QW
99
SE
4
967
RR
EMP SUMM :
EMP ID
EMP NAME
Age
Amt
Relation
5
E
4
2333
QW
6
F
2
45
RR
1
A
23
67
QW
11
AA
18
45
RR
12
BB
13
45
QW
13
CC
31
7777
QW
14
DD
80
5
QW
15
FF
11
2
RR
17
F
23
89
QW
18
CC
1
1200
QW
36
WW
4
454
RR
36
QG
6
65
RR
22
TB
2
156
RR
I need to apply Relation filter to visualization are not changing/applying .
1)
More test =
var _distDet =
DISTINCT(Sheet1[EMP ID _DE] ) //unique Details Ids
var _distSumm =
DISTINCT('Sheet1 (2)'[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
2)
More test_Unmatch =
var _distDet =
DISTINCT(Sheet1[EMP ID _DE] ) //unique Details Ids
var _distSumm =
DISTINCT('Sheet1 (2)'[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
I do have two visualization More test and More test _Un-Matched ,which are un-match record of each other table.
I am trying to apply Slicer but its not applying Properly on visualization ,
Relationship between table :
There is Many to One relationship between the table.
Both table have Relation Field ,we can use any one of them to fiter.
Expected Result looking for:
If I select QW or RR from Slicer it should filter Un-Matched record from More test and Moretest_Unmatch from two different table.
For EMP details table:
QW Count :5
RR Count: 3
For Emp Summ table:
QW Count: 5
RR Count 1
EMP ID
EMP NAME
Age
Amt
Relation
Vlookup
5
E
4
2333
QW
#N/A
13
CC
31
7777
QW
#N/A
14
DD
80
5
QW
#N/A
17
F
23
89
QW
#N/A
18
CC
1
1200
QW
#N/A
22
TB
2
156
RR
#N/A
EMP ID _DE
EMP Name_De
Age
amt
Relation
Vlookup
2
B
2
45
QW
#N/A
3
C
3
2000
QW
#N/A
4
D
6
3444
QW
#N/A
23
GG
34
6666
QW
#N/A
56
A
44
3333
RR
#N/A
34
C
16
3555
RR
#N/A
41
F
11
3444
QW
#N/A
99
SE
4
967
RR
#N/A
Please can you help on this.
Thanks & Regards,
Rakesh Jadhav
v-abhinavmuHi Rakesk13,
Thank you for providing the details.
I tried reproducing the scenario using the sample data and DAX shared in the post. With the provided data, I was able to reproduce the unmatched counts, but the results don't align with the expected output (QW = 5/5 and RR = 3/1). It appears that the sample data and the expected results may not be consistent (for example, some matching EMP IDs have different Relation values across the two tables).
Could you please share a reproducible .pbix file or a correct sample dataset that exactly reflects the issue you're facing? That will help us reproduce the problem accurately and provide the appropriate solution.
Hope this helps. If you have any doubts regarding on this thread, please feel free to reach out to us. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
- Rakesk13
Hi team,
I have created calculation :
Column = IF(ISBLANK(LOOKUPVALUE('EMP Details'[EMP ID_DE],'Sheet1 (2)'[EMP ID],'EMP Details'[EMP ID_DE])),"NA","Record")Please can you help on this.Thanks & Regards,Rakesh Jadhav - Rakesk13
Hi,
Yes,From Emp Summary table Record don't exist in EmpDetail whats what i am looking for.
Not a Count ,looking for Summary of un-match values.
From that Un-match record from Emp Summ table we have other table as
EMP_ADD as:
EMP NAME
EMP ADDRESS
E
XX1X
BB
ERE3R
FF
JHJ8Q
From that Un-match record of EMP Summ and EMP_ADD
we need to get matching value from EMP_ADD and EMP Summ .
Output as:
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
- v-pnaroju-msft
Hi Rakesk13,
We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.
Thank you. - v-pnaroju-msft
Hi Rakesk13,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.
