Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Relationship with 3 tables in Direct Query

Hello everyone,

 

Need a little help. I have 3 tables like below:

If I want to calculate Sales amount by Item group which the Sales Order Status is "Closed". What will be the DAX ?

I was thinking I need to join those Sales Order and Order Lines first, but I cannot do that because I'm must use Direct Query and creation of tables will not be allowed. 

 

Is there a workaround ? 

 

Much appreciated if someone can help me on this.

Thanks,

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

     

    To count sales id: 

     

    calculate( distinctcount(  order line[sales id ] ),   Sales order[status]="closed")

     

    to count workers:

     

    calculate( count( sales order[workers] ),   Sales order[status]="closed", order line)

     

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

7 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    this its a example of the SQL statement you could be using or a variation of it using leftouter relantionship according to your need
    SELECT * FROM [table1] JOIN [table2] ON [table1.primary_key] = [table2.foreign_key];

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Create the following measure and place it on table visual with Group field,

    Sales Amount = CALCULATE(SUM(OrderLine[Amount]),SalesOrder[Status]="Closed")

    ________________________

    If my answer was helpful, please click Accept it as the solution to help other members find it useful

    Click on the Thumbs-Up icon if you like this reply 🙂


    Website YouTube  LinkedIn

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    you can set your direct query tables to dual mode storage mode, this should enable you to create new tables based on the direct query table you changed to dual mode. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

       

      Unfortunately, I can't use Dual mode as well. The rule here is 'pure' Direct Query 😅

       

      Fowmy , yeah for the amount measure I have created like your example, and after put in the Group, all rows displaying same value (which is the total), is like the filter on the relationship is not working

       

      Thanks

       

       

       

      • StefanoGrimaldi's avatar
        StefanoGrimaldi
        Resident Rockstar
        5 years ago

        in that case them you could create the data table by making a new direct query and using SQL statement in the advance option segment of the data source to make the source return you the table joined with both table information, basically make the relationship join on the source and get it to your data model. 

         

        here you state that table join to the source to handle and return the combined table (on SQL languague).