cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ja832153
Post Patron
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
POLNUMDATEBROKERAMOUNTCITY
11-JanAA100A
21-FebAB200B
31-MarAC300C
41-AprAD400D
51-MayAE500K
61-JunAF600F
                                     Table2
POLNUMDATEBROKERAMOUNTCITY
11-JanAA100A
21-FebAB200B
31-MarAC300C
41-AprAG450H
51-MayAE500E
61-JunAH650F
OutPut ( Compare Table1 values vs Table2)
POLNUMTotal Columns(Inc Pol Num Column)Columns MatchColumns Mismatch% Matching
1550100%
2550100%
3550100%
453260%
554180%
653260%
2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @ja832153 

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 

 

SU18_powerbi_badge

View solution in original post

v-yuaj-msft
Community Support
Community Support

Hi @ja832153 ,

 

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:

v-yuaj-msft_0-1608533645887.png

 

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.

View solution in original post

4 REPLIES 4
v-yuaj-msft
Community Support
Community Support

Hi @ja832153 ,

 

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:

v-yuaj-msft_0-1608533645887.png

 

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.

View solution in original post

AlB
Super User
Super User

Hi @ja832153 

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 

 

SU18_powerbi_badge

View solution in original post

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

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.