cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JulietZhu Member
Member

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. TransactionTypeID= 4 and billdate between '2018-08-01' and '20180831'

 

I have two DAX. One is from Bill table, and the other one is from transction table. They have one to many relationship.

But both DAX give me different number from SQL statement. Can anyone help to see what is wrong with my DAX. Thanks.

 

1.PNG2.PNG

5 REPLIES 5
Community Support Team
Community Support Team

Re: DAX help from table join

Hi @JulietZhu,

 

Can you share the relationship? It's better to have the file if possible.

1. How does "Date" table connect with other tables?

2. Are the relationships set to filter both? The "Cross Filter Direction" setting.

3. Are all the IDs in both tables?

I think the [Bill] shouldn't be filtered.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JulietZhu Member
Member

Re: DAX help from table join

Here is relationship.

 

Capture.PNG

AkhilAshok Established Member
Established Member

Re: DAX help from table join

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.

Community Support Team
Community Support Team

Re: DAX help from table join

Hi @JulietZhu,

 

@AkhilAshok just explained and gave the solution. Please try it out in your model.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JulietZhu Member
Member

Re: DAX help from table join

@v-jiascu-msft@AkhilAshok, the DAX I used actually is correct becuase when I only choose those 3 tables in my data modeling, both DAX I used are correct. Since there are other tables in data modeling, several ways can do calcuation. That is why whatever DAX are used, it won't give the correct answer.

 

The solution for this is that I write sql query in database and bring in another fact table in my data modeling. Thanks for helping.