Forum Discussion

JulietZhu's avatar
JulietZhu
Helper IV
7 years ago

DAX help from table join

Need translate those sql into DAX.  SELECT COUNT(DISTINCT b.billId) AS BilledCount   FROM Bill b WITH (NOLOCK) LEFT JOIN dbo.Transactions t       ON (b.Billid = t.billid) WHERE t. TransactionTyp...
Need Help
AkhilAshok's avatar
AkhilAshok
Solution Sage
7 years ago

Why not try this way:

Billed Count =
CALCULATE (
    DISTINCTCOUNT ( Transactions[billId] ),
    Transactions[TransactionTypeID] = 4
)

The filter on TransactionTypeID won't flow to Bill table (since it is the one side of relationship). So, if you take distinctcount billID fron Bill table, then you have to either enable bi-directional filtering between Bill & Transactions, or use CROSSFILTER function in DAX. A more performing approach is to just take Distinctocunt of BillID from Transactions table as I showed.