Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Relationship with 3 tables in Direct Query

Hello everyone,

 

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

medwong_0-1608837372531.png

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,

 

1 ACCEPTED SOLUTION

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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];





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

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

 

 

 

Anonymous
Not applicable

Hi @Fowmy ,

 

Sorry my mistakes, if sum sales amount, which is on the Transactions table side.. it is working. But actually there is another measure which Count of Sales Id... by which I counting the one in Sales Table instead.

 

So do I need to count Sales Id in Trans instead ? 

Just one question what if there is other field in Sales Table that I want to count ? So forexample, I added new column in Sales Id, lets say 'Worker", and I want to count this, like :

 

Count worker by Group if Status = Closed

 

Thanks,

 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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. 

 

StefanoGrimaldi_0-1608862934966.png

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.