cancel
Showing results for
Did you mean:
Post Patron

DAX formula help needed

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%
2 ACCEPTED SOLUTIONS
Super User

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"}),
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),
in

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

Community Support

Hi @ja832153 ,

Based on your description, you can create some measures as follows.

`Total Columns(Inc Pol Num Column) = 5Columns 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)returnIF(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.

4 REPLIES 4
Community Support

Hi @ja832153 ,

Based on your description, you can create some measures as follows.

`Total Columns(Inc Pol Num Column) = 5Columns 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)returnIF(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.

Super User

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"}),
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),
in

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

Post Patron

@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.

Post Patron

Thanks @AlB . I will check this M Query and let you know.

Announcements

Launching new user group features

Learn how to create your own user groups today!