Create DAX Formula Question

[tsdick]

The following 2 MSAccess queries produce a count of models where the equip ID ledger account amount sum is not $0.  I would like to recreate in PBI using DAX but I cannot find the right combinations of DAX functions and/or nesting to filter, sum, group by, and count distinct.  Need some help…

Q2 (Using output from Q1 counts by model the number of equip ID’s

SELECT q1.Model, Count(q1.Model) AS CountOfModel

FROM q1

GROUP BY q1.Model;

Q1 (aggregates multiple equip ID amounts by ID and model when equip ID amounts sum is not $0

SELECT raw.[Equip ID], raw.Model, Sum(raw.[Amount in transaction currency]) AS [SumOfAmount in transaction currency]

FROM raw

WHERE (((Left([Ledger account],1))=4))

GROUP BY raw.[Equip ID], raw.Model

HAVING (((Sum(raw.[Amount in transaction currency]))<>0));

Timothy.**bleep**@zieglercat.com

[ryan_mayu · Accepted solution]

@tsdick 

could you pls provide some sample data and expected output?

[tsdick]

I have a sample but don't know how to attach an excel file to the reply.  Can I email direct to you?  Thanks for offering to help.

Tim