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
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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

JulietZhu's avatar
JulietZhu
Helper IV
7 years ago

Here is relationship.

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi JulietZhu,

     

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

     

     

    Best Regards,
    Dale

    • JulietZhu's avatar
      JulietZhu
      Helper IV
      7 years ago

      v-jiascu-msftAkhilAshok, 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.