DAX formula help needed

[Anonymous]

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%

[AlB · Accepted 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 

[Anonymous · Accepted solution]

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.

[AlB · Accepted 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 

[Anonymous]

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

[Anonymous]

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