Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi ,
I have below requriment ( compare the values between table 1 vs table2). How do we achive it?
Sample data:
Table1 | ||||
POLNUM | DATE | BROKER | AMOUNT | CITY |
1 | 1-Jan | AA | 100 | A |
2 | 1-Feb | AB | 200 | B |
3 | 1-Mar | AC | 300 | C |
4 | 1-Apr | AD | 400 | D |
5 | 1-May | AE | 500 | K |
6 | 1-Jun | AF | 600 | F |
Table2 | ||||
POLNUM | DATE | BROKER | AMOUNT | CITY |
1 | 1-Jan | AA | 100 | A |
2 | 1-Feb | AB | 200 | B |
3 | 1-Mar | AC | 300 | C |
4 | 1-Apr | AG | 450 | H |
5 | 1-May | AE | 500 | E |
6 | 1-Jun | AH | 650 | F |
OutPut ( Compare Table1 values vs Table2) | ||||
POLNUM | Total Columns(Inc Pol Num Column) | Columns Match | Columns Mismatch | % Matching |
1 | 5 | 5 | 0 | 100% |
2 | 5 | 5 | 0 | 100% |
3 | 5 | 5 | 0 | 100% |
4 | 5 | 3 | 2 | 60% |
5 | 5 | 4 | 1 | 80% |
6 | 5 | 3 | 2 | 60% |
Solved! Go to Solution.
Hi @Anonymous
This can be best done in Power Query. Place the following M code in a blank query to see the steps.See it all at work in the attached file.
let
Source = Table.SelectColumns(Table1,{"POLNUM"}),
#"Added Custom" = Table.AddColumn(Source, "Total cols", each Table.ColumnCount(Table1), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Cols match", each
let
t1_ = Record.ToList(Table1{[POLNUM = [POLNUM]]}),
t2_ = Record.ToList(Table2{[POLNUM = [POLNUM]]}),
aux_= List.Numbers(0,[Total cols]),
res_ = List.Sum(List.Transform(aux_, each Number.From(t1_{_}=t2_{_})))
in
res_, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Cols mismatch", each [Total cols] - [Cols match], Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Cols matching %", each [Cols match]/[Total cols], Percentage.Type)
in
#"Added Custom3"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @Anonymous ,
Based on your description, you can create some measures as follows.
Total Columns(Inc Pol Num Column) = 5
Columns Mismatch =
var x1=IF(MAX('Table1'[POLNUM])<>SELECTEDVALUE(Table2[POLNUM]),1,0)
var x2=IF(MAX('Table1'[DATE])<>SELECTEDVALUE(Table2[DATE]),x1+1,x1)
var x3=IF(MAX('Table1'[BROKER])<>SELECTEDVALUE(Table2[BROKER]),x2+1,x2)
var x4=IF(MAX('Table1'[CITY])<>SELECTEDVALUE('Table2'[CITY]),x3+1,x3)
return
IF(MAX('Table1'[AMOUNT])<>SELECTEDVALUE('Table2'[AMOUNT]),x4+1,x4)
Columns Match = [Total Columns(Inc Pol Num Column)]-[Columns Mismatch]
% Matching = DIVIDE([Columns Match],[Total Columns(Inc Pol Num Column)])
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Based on your description, you can create some measures as follows.
Total Columns(Inc Pol Num Column) = 5
Columns Mismatch =
var x1=IF(MAX('Table1'[POLNUM])<>SELECTEDVALUE(Table2[POLNUM]),1,0)
var x2=IF(MAX('Table1'[DATE])<>SELECTEDVALUE(Table2[DATE]),x1+1,x1)
var x3=IF(MAX('Table1'[BROKER])<>SELECTEDVALUE(Table2[BROKER]),x2+1,x2)
var x4=IF(MAX('Table1'[CITY])<>SELECTEDVALUE('Table2'[CITY]),x3+1,x3)
return
IF(MAX('Table1'[AMOUNT])<>SELECTEDVALUE('Table2'[AMOUNT]),x4+1,x4)
Columns Match = [Total Columns(Inc Pol Num Column)]-[Columns Mismatch]
% Matching = DIVIDE([Columns Match],[Total Columns(Inc Pol Num Column)])
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
This can be best done in Power Query. Place the following M code in a blank query to see the steps.See it all at work in the attached file.
let
Source = Table.SelectColumns(Table1,{"POLNUM"}),
#"Added Custom" = Table.AddColumn(Source, "Total cols", each Table.ColumnCount(Table1), Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Cols match", each
let
t1_ = Record.ToList(Table1{[POLNUM = [POLNUM]]}),
t2_ = Record.ToList(Table2{[POLNUM = [POLNUM]]}),
aux_= List.Numbers(0,[Total cols]),
res_ = List.Sum(List.Transform(aux_, each Number.From(t1_{_}=t2_{_})))
in
res_, Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Cols mismatch", each [Total cols] - [Cols match], Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Cols matching %", each [Cols match]/[Total cols], Percentage.Type)
in
#"Added Custom3"
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@AlB - This solution is working as excepted . But the problem was getting an error after mutiple runs.
Preview.Error: The current preview value is too complex to display.
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |