DAX formula help needed

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.