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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Dhruvin
Helper III
Helper III

What is the best way to apply Nested Relationship same as SQL Statements?

Hello,

 

I'm trying to achieve the following SQL Statement's result into Power BI.

SELECT Coalesce(c.name, 'Andre Transportører') AS name, 
       Count(*)                                 Bookings, 
       Coalesce(t2.totinv, 0)                   AS Invoiced, 
       Coalesce(t2.freightcost, 0)              AS InvFreight, 
       Count(*) - Coalesce(t2.totinv, 0)        AS NotInvoiced, 
       Coalesce(t2.freightcost, 0) / Coalesce(t2.totinv, 1) * ( Count(*) - 
       Coalesce(t2.totinv, 0) )                 AS Avsetning 
FROM   carriers c 
       right join tplbookings t 
               ON c.id = t.carrierid 
       left join (SELECT i.carrierid, 
                         Coalesce(Count(*), 0)                        AS TotInv, 
                         SUM(Coalesce(w.netamount, 0) 
                             + Coalesce(w.totalamountfromcharges, 0)) AS 
                         FreightCost 
                  FROM   waybills w 
                         inner join invoices i 
                                 ON w.invoiceid = i.id 
                  WHERE  i.invoicestatus <> 'Draft' 
                         AND i.customerid = 1067 
                         AND w.departuredate BETWEEN 
                             '2019-06-01' AND '2019-06-30' 
                  GROUP  BY i.carrierid) t2 
              ON t.carrierid = t2.carrierid 
WHERE  t.customerid = 1067 
       AND departuredate BETWEEN '2019-06-01' AND '2019-06-30' 
GROUP  BY c.name, 
          t2.totinv, 
          t2.freightcost 
ORDER  BY Count(*) DESC

I 'm getting the following end result with above SQL Statement.

Screenshot_3.png

Now, I have tried to apply the same relationship within my Power BI tables and and take all the parameters from the where conditions as a slicer in Power BI and Select statement as a column from table. Still I'm not getting the correct result.

 

What I'm missing here in relationship? Or what is the best way to achieve this type of result in Power BI?

Screenshot_4.png

 

Any help really appriciated !


Thanks

1 REPLY 1
v-frfei-msft
Community Support
Community Support

Hi @Dhruvin ,

 

SUMMARIZECOLUMNS should work.

https://docs.microsoft.com/en-us/dax/summarizecolumns-function-dax

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors