IF related table contains values in multiple columns then update table A with a defined value..

[ToddMate]

Hi,

I have two tables, an Accounts table and a Services table.

The Accounts table contains the usual customer information and is linked to the Services table by the Account Number in a one to many relationship.

With the Services table I have a Status field and a Product field.

I am trying to determine if an Account has one or more Connected Mobiles on it.

The criteria would be:

Product = “Mobile”

Status: “Connected” or “In Progress”

I would like to store the value in the Accounts table with a “Yes” or “No” for future reporting.

Is anyone able to assist with this.

Cheers,

Todd

[chayanupadhyay]

@ToddMate  I have tried the same scenario with sample data, PFB the screenshot since i am unable to attach the data here:

first two is the sample data: Account table and service table:

'

I have created a calculated column in Account table with below DAX:

IF(CALCULATE(FIRSTNONBLANK(service[status],1),FILTER(service,Account[Account No]=service[account no]),service[Product]="Mobile")="Connected","Yes",IF(CALCULATE(FIRSTNONBLANK(service[status],1),FILTER(service,Account[Account No]=service[account no]),service[Product]="Mobile")="In Progres","No",""))

and then i selected Account No and the custom column, PFB the snapshot:

I hope this what you are expecting and you can change the DAX logic in case i misunderstood the Yes and No logic.

Please let me know if you need any explaination....Hope this will help !!

Regards,

Chayan Upadhyay

[mussaenda]

If you can provide a sample data to help you easier

[ToddMate]

@mussaenda , 

I can't attach a file in this forum post? I have done a screenshot of both sample tables: (relationship is One to Many on Account_Number)

The outcome i am trying to achieve is to populate the "Has Mobile" column (Yes/No) on the Accounts table where the criteria in the Services table is:

Product = Mobile

Status = Connected

Using this criteria i would like to see "Yes" in against Account Number 123 & 126. 127 would be No as the service is Disconnected and No should also appear for everything that is null. 

Accounts Table
Services Table
Relationship

[Mariusz · Accepted solution]

Hi @ToddMate 

Or you can add this column 

Has Mobile = 
VAR __checks =
COUNTROWS( 
    FILTER( 
        RELATEDTABLE( 'Services Table' ), 
        'Services Table'[Product] = "Mobile" 
        && 'Services Table'[Status] = "Connected"
    )
) > 0
RETURN 
IF( __checks, "Yes", "No" )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[ToddMate]

@Mariusz  PERFECT! Thank you so much for your help.

[Mariusz]

Hi @ToddMate 

You can use query editor for that, just copy this three tables in to Blank Queries.

Services Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8s1PysxJBTKc8/PyUpNLUlOUYnVgkn5OfjhkAjLy87DpMoHrcsksTkaTNAUKO7oE+2DRZ4ZPnxk+d+KVNEeWxGKyBXavxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account Number" = _t, Product = _t, Styatus = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number", Int64.Type}, {"Product", type text}, {"Styatus", type text}})
in
    #"Changed Type"

Services Pivot Product

let
    Source = #"Services Table",
    #"Filtered Rows" = Table.SelectRows(Source, each ([Styatus] = "Connected")),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Connected","Yes",Replacer.ReplaceText,{"Styatus"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Product]), "Product", "Styatus", List.Median )
in
    #"Pivoted Column"

Accounts Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8srMBZLBuZklGQpKsTogYROggFtRagpINj8vtRgqbArkeyQWFVUC6fCMzJJUqLgZkO+bmZ0KpIKAmiCC5kCeX2JRXipItXtRamoeVMICbCpIcXhmTk5mYi7Q+FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account Number " = _t, FirtName = _t, LastName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Number ", Int64.Type}, {"FirtName", type text}, {"LastName", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Account Number "}, #"Services Pivot Product", {"Account Number"}, "Services Pivot Product", JoinKind.LeftOuter),
    #"Expanded Services Pivot Product" = Table.ExpandTableColumn(#"Merged Queries", "Services Pivot Product", {"Mobile"}, {"Mobile"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Services Pivot Product",null,"No",Replacer.ReplaceValue,{"Mobile"})
in
    #"Replaced Value"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.