Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

SU18_powerbi_badge

View solution in original post

v-yuaj-msft
Community Support
Community Support

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:

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

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.

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

Anonymous
Not applicable

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.