Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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" )
@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:
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
If you can provide a sample data to help you easier
@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.
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" )
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"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |