Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
Any help really appriciated !
Thanks
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.