Forum Discussion

Dhruvin's avatar
Dhruvin
Icon for Helper III rankHelper III
7 years ago

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...
Need Help
Show and Tell
Tips and Tricks
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

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