cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dhruvin Member
Member

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
Stachu Super Contributor
Super Contributor

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

I assume that the table you attached is what you want your visual look like, correct?

Do you need to prepare such table for single customer (1067) or multiple customers?

 

I'd start with the following:
1) put Carriers[Name] in the rows of the table visual

2) create first measure - Bookings

Bookings = COUNTROWS('TplBookings')

and put in the visual from 2)

You should now see all the carriers and number of rows from the TplBookings that is related to them

 

now if you want to limit it to a single customer you can either filter in the visual by the customer name (e.g. by using slicer) or hardcode it in CALCULATE (in this case you need single measure for each customer if you need to show more, in that case it's better to use the slicer)

Bookings = CALCULATE( COUNTROWS('TplBookings'), 'Invoices'[CustomerID] = 1067)

 3) continue with the other measures using DAX

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 350 members 3,189 guests
Please welcome our newest community members: