Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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,
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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];
Proud to be a Super User!
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.
Proud to be a Super User!
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
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 🙂
⭕ 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.
here you state that table join to the source to handle and return the combined table (on SQL languague).
Proud to be a 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
82 | |
61 | |
61 | |
60 |