DAX IF Querying 2nd table with filter returning a value from another column

[blazer12219]

Hello All,

I have two separate tables their names are AR.TrxHistoryHdr and AR.TrxHistoryDtl both are related.  The *.Hdr table has most of the data I need but there is also some conditional data I need to get from the Dtl table based upon a filter than return that data in a row back into the Hdr table.

What I would like to do is filter the column AR.TrxHistoryDtl.ItemNo for "TAX" than based upon that filter return the results in by row for data located in column AR.TrxHistoryDtl.SaleAmt1.  Placing that data in a new custom column located in the AR.TrxHistoryHdr table anyother non-matching results should equal 0.

I can get the data back based upon the attached screen shoot but if filters everything based on the "TAX" filter but I need the other data as well.  So I am sure its the answer is some type of IF statement filtered but cannot get the formula down to work and keep all the other data as well. -thx

[v-lid-msft]

Hi @blazer12219 ,

We can add a custom column using Table.SelectRows function to meet your requirement in power query edior using M Formula.

let no = [Ar.TrxHistoryHdr.ItemNo],
t = 
Table.SelectRows(#"AR TrxHistoryDtl",each [Ar.TrxHistoryDtl.ItemNo] = no) 
in 
if Table.RowCount(t)=0 then 0 else t

Then you can expand the new column to AR.TrxHistoryDtl.SaleAmt1 column by rows.If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

Best regards,

[blazer12219]

So that formula did not work, I believe it is because I didn't explain correctly.

Trying to create new column called AdjItemTax in the Ar.TrxHistoryHdr table with row results first by filtering the Ar.TrxHistoryDtl table ItemNo. Column of all its items selecting only the ItemNo.="Tax"

If you find true results that = "Tax" than bring me back the result from Ar.TrxHistoryDtl table [SaleAmt1] into the new column row otherwise false result a result equal to just 0=zero

What happend is had some employee unknown to me include Tax as an ItemNumber.  So the Tax is included in the Gross Sale.  Once I get this AdjItemTax Amount I can create another column that subtracts this number from the Gross Sale to get AdjGross Sale.

Problem I ran into is keeps saying does not recognize AR.TrxHistoryDtl data probably because formula is accessing another table.  If I expand that table than I get way more detail and brings in every line item of every invoice.

Help version

let no = [Ar.TrxHistoryHdr.ItemNo],
t =
Table.SelectRows(#"AR TrxHistoryDtl",each [Ar.TrxHistoryDtl.ItemNo] = no)
in
if Table.RowCount(t)=0 then 0 else t

My best guess at CORRECTED VERSION
let no = [Ar.TrxHistoryDtl.ItemNo],
t =
Table.SelectRows(#"AR TrxHistoryDtl",each [Ar.TrxHistoryDtl.ItemNo] = "TAX")
in
if Table.RowCount(t)=[Ar.TrxHistoryDtl.SaleAmt1] then [TaxableAmt] else t

[Jimmy801]

Hello @blazer12219 

to make a summary of what your need is

- two table that have a criteria where they can be connected

- in the header-table you need the joined detail-table, applying a filter, and if there are some lines, pass the first cell of different column otherwise 0

I think the if statement has to be kicked in before the joined column are expanded. As i see in your screenshot, you are trying to apply it afterwards.

Here an example

// Header
let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGmuFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Purchase order" = _t]),
    ChangedType = Table.TransformColumnTypes(Quelle,{{"Purchase order", Int64.Type}}),
    Join = Table.NestedJoin(ChangedType, {"Purchase order"}, Detail, {"Purchase order"}, "Detail", JoinKind.LeftOuter),
    GetAmount = Table.TransformColumns
    (
        Join,
        {
            {
                "Detail",
                (table)=>
                let 
                    GetFilteredTable = Table.SelectRows(table, each [ItemNo] = "Tax")

                in 
                    if Table.IsEmpty(GetFilteredTable) then null else GetFilteredTable[SaleAmt1]{0}

            }
        }
    )
in
    GetAmount

// Detail
let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrACSpkqxOhB+fklGahFWETOwiBFcjzmcD1NhgabCEm4GhG9oAFfgD9ViaIgpZAQWMkYy2NBYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Purchase order" = _t, ItemNo = _t, SaleAmt1 = _t]),
    ChangedType = Table.TransformColumnTypes(Quelle,{{"Purchase order", Int64.Type}, {"ItemNo", type text}, {"SaleAmt1", Int64.Type}})
in
    ChangedType

Copy both code to the advanced editor and name the query as indicated in the header row to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[blazer12219]

let
    Source = Sql.Databases("Ventus"),
    RSCMechanical = Source{[Name="RSCMechanical"]}[Data],
    AR_TrxHistoryHdr = RSCMechanical{[Schema="AR",Item="TrxHistoryHdr"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(AR_TrxHistoryHdr,{"Oid", "ApplyToInvoiceNo", "SoldToAdd2", "ShipToCustomerNo", "OtherAmt", "FrgtAmt", "FrgtCode", "PaymentApplied", "SalesTaxAmt2", "SalesTaxAmt3", "SalesTaxAmt4", "SalesTaxAmt5", "SalesTaxAmt6", "UseTaxCode", "UseTaxAmt1", "UseTaxAmt2", "UseTaxAmt3", "UseTaxAmt4", "UseTaxAmt5", "UseTaxAmt6", "InvoiceCostAmt", "RetainageBillFlag", "RetainageOid", "TrxAmtBilled", "JobNo", "TermCode", "SoldToName", "ShipToName", "OriginalTicketNo", "ContractType", "RegHrsBill", "RegHrsNon", "RegHrsWork", "OvtHrsBill", "OvtHrsNon", "OvtHrsWork", "DblHrsBill", "DblHrsNon", "DblHrsWork", "MileAmt", "LaborSaleAmt", "LaborTaxAmt", "TravelSaleAmt", "TravelTaxAmt", "FixedSaleAmt", "FixedTaxAmt", "PartsSaleAmt", "PartsTaxAmt", "MiscSaleAmt", "MiscTaxAmt", "SubSaleAmt", "SubTaxAmt", "PMRevenueAmt", "CntrBillAmt", "CntrBillTaxAmt", "LaborCostBill", "LaborCostNon", "TravelCostBill", "TravelCostNon", "PartsCostBill", "PartsCostNon", "MiscCostBill", "MiscCostNon", "SubCostBill", "SubCostNon", "PayAmt", "PayAmt2", "PayAmt3", "PayMthd", "PayMthd2", "PayMthd3", "BillToCustomerNo", "CallType", "PrintInvoice", "PrimaryTechCode", "InvoiceFormatCode", "SubInvoiceNo", "SubInvoiceDate", "SubInvoiceAmt", "BillToSiteNo", "PaymentStatus", "PreviousFixedAmt", "ScheduleDate", "ProblemCode", "UnitNo", "AuthorizedBy", "EntryDate", "LeadSourceCode", "EstHrs", "WebTicketAproved", "WebDropTicket", "WebCall", "WebCallType", "NtxPoAmt", "ConfirmedWith", "CreditCardNo", "CreditCardExpireDate", "WebUserId", "Salesman", "Territory", "BillType", "BillDivision", "Department", "HoldReasonText1", "HoldReasonText2", "InitialsPlacedHold", "TrxStatusFlag", "OrderDate", "OrderNo", "ShippedDate", "ShipViaCode", "ReqShipDate", "CollectPpd", "GLMiscAcct", "GLCashAcct", "FCCode", "CntrBillStrDate", "CntrBillEndDate", "OvertimeApproved", "QuoteApproved", "QuoteApprovedDate", "QuoteDeniedDate", "CommentInternalText", "CommentCustomerText", "CommentSubcontractorText", "CommentApproved", "PostedFromAppl", "PostedFromType", "PostFlag", "SignoffBy", "PlacedBy", "PostedBy", "Reversed", "Closed", "CnvCode", "SeqNo", "PostControl", "OptimisticLockField", "OriginalTicket", "BatchNo", "PaymentMethod", "Desc", "HandlingChargeAmt", "FixedPriceContractAmt", "CntrBillCode", "CnvCode2", "AuditControl", "BalancedFlag", "ShipViaService", "ShipViaServiceCallType", "ShipViaServiceDispatcher", "ShipViaServiceEmployeeNo", "ShipViaServiceJobNo", "ShipViaServiceProblemCode", "ShipViaServiceSiteNo", "ShipViaServiceTicketNo", "CertificateOfLiabilityInsurance", "DamageWaiverFee", "DamageWaiverFeeOverride", "DepositAmt", "DepositPostControl", "DepositReturnAmt", "DepositReturnOption", "OrderType", "RetainageInvoiceOid", "ExciseTaxAmt", "UserFurnishedMaterialAmt", "UserFurnishedMaterialNonTaxable", "PropertyManagerNo", "QuoteNo", "BalanceForwardPaymentApplied", "InvoicedFlag", "ModifiedCompletedContractThruDate", "CheckCreated1DateNotified", "CheckCreated1NumberTimesNotified", "CheckCreated2DateNotified", "CheckCreated2NumberTimesNotified", "PastDueInvoice1DateNotified", "PastDueInvoice1NumberTimesNotified", "PastDueInvoice2DateNotified", "PastDueInvoice2NumberTimesNotified", "RetainageBilled1DateNotified", "RetainageBilled1NumberTimesNotified", "RetainageBilled2DateNotified", "RetainageBilled2NumberTimesNotified", "UnbilledRetainage1DateNotified", "UnbilledRetainage1NumberTimesNotified", "UnbilledRetainage2DateNotified", "UnbilledRetainage2NumberTimesNotified", "DueDate", "EtaDate", "TrackNo", "MobileDataFlag", "AR.TrxHistoryAttachedDocument", "AR.TrxHistoryBilledRetainage(Oid)", "AR.TrxHistoryBilledRetainage(Oid) 2", "AR.TrxHistoryEstimateData", "AR.TrxHistoryTimeStamp"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([TrxType] <> 1) and ([Year] = 2019))
in
    #"Filtered Rows"

// Header
let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGmuFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Purchase order" = _t]),
    ChangedType = Table.TransformColumnTypes(Quelle,{{"Purchase order", Int64.Type}}),
    Join = Table.NestedJoin(ChangedType, {"Purchase order"}, Detail, {"Purchase order"}, "Detail", JoinKind.LeftOuter),
    GetAmount = Table.TransformColumns
    (
        Join,
        {
            {
                "Detail",
                (table)=>
                let 
                    GetFilteredTable = Table.SelectRows(table, each [ItemNo] = "Tax")

                in 
                    if Table.IsEmpty(GetFilteredTable) then null else GetFilteredTable[SaleAmt1]{0}

            }
        }
    )
in
    GetAmount
// Detail
let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrACSpkqxOhB+fklGahFWETOwiBFcjzmcD1NhgabCEm4GhG9oAFfgD9ViaIgpZAQWMkYy2NBYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Purchase order" = _t, ItemNo = _t, SaleAmt1 = _t]),
    ChangedType = Table.TransformColumnTypes(Quelle,{{"Purchase order", Int64.Type}, {"ItemNo", type text}, {"SaleAmt1", Int64.Type}})
in
    ChangedType

Couple questions dropped this and brought back an Token Eof expected error.

Couple questions don't understand what the "#Purchase Order" in your code is for plus the function of the (Binary.FromText("i45WMlTSUQpJrACSpkqxOhB+fklGahFWETOwiBFcjzmcD1NhgabCEm4GhG9oAFfgD9ViaIgpZAQWMkYy2NBYKTYWAA==" code.  Recall the table date that includes the ItemNO and SaleAmt1 columns are located in the AR_TrxHistoryDtl table.  Name of new column with results = "AdjTaxSaleAmt"

Could a CALCULATE and ALL function also be useful in this situation alternatively?

Thanks for you help I never dreamed this formula would get this complicated so appreciate you working with me.

[Jimmy801]

Hello

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[Jimmy801]

Hello @blazer12219 

to check if my solution is working, create new blank queries, name it Header and Detail and copy in my code in the advanced editor.

Now you can check if this could be a solution for you. If yes, I could help implement it in your real scenario.

All the best

Jimmy