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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Add rows

Hi, I'm starting with Powr BI and I'm getting to know the DAX features.

I'd like to know if there's a way to do the following with DAX.

I have a table with the following data:

TeamOriginDestinationDeparture DateArrival DateDistance kmsTotal Distance
T1Planta1Almacen101/03/2021 02:0101/03/2021 10:01150292
T1Almacen1Planta101/03/2021 11:1001/03/2021 19:01142
T1Planta1Almacen202/03/2021 03:0102/03/2021 12:01210420
T1Almacen2Planta102/03/2021 13:1002/03/2021 22:01210
T1Planta1Bodega103/03/2021 02:0103/03/2021 02:317299
T1Bodega1Almacen103/03/2021 03:0103/03/2021 10:01150
T1Almacen1Planta103/03/2021 11:1003/03/2021 19:01142

Each row represents a section of the route and its distance, I need to be able to add the distance of the different sections and put it in a total, this in excel I do with formulas, but with DAX I can not find the way.

Thank you for your attention, greetings...

12 REPLIES 12
v-xiaoyan-msft
Community Support
Community Support

Hi@Syndicate_Admin

 

Nice day ! Has your problem been solved ? if so, please consider Accept a correct reply as the solution to help others find it.
 
Best Regards,
Caitlyn Yan
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Syndicate_Admin @Anonymous 

 

Not very clear, the total is currently based on Team and Date of Departure date, you can do M or DAX. What if you want to calculate different conditions? And you want a measure or calculated column?

 

M

 

 

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "nZCxCoNAEER/JVwtuLsTCbFLvsAinVgcKmmMNv4/4XBhT9fKbnkwj9lp2/DhUIRmivMa0/WafrEf53QSl4RSSPhGUtMBMW2IKwpdoZ4sbco8xDXTAT3VcxfzuD6SQmJ9oH0MsVYU9n1k3ycLQfsYkjOPhd/LMH43Dfw8O4SEHiaxaD4y/FO4MjL8yDgfufsD",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        Team = _t,
        Origin = _t,
        Destination = _t,
        #"Departure Date" = _t,
        #"Arrival Date" = _t,
        #"Distance kms" = _t
      ]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {
      {"Team", type text},
      {"Origin", type text},
      {"Destination", type text},
      {"Departure Date", type datetime},
      {"Arrival Date", type datetime},
      {"Distance kms", Int64.Type}
    }
  ),
  #"Inserted Date" = Table.AddColumn(
    #"Changed Type",
    "Date",
    each DateTime.Date([Departure Date]),
    type date
  ),
  #"Grouped Rows" = Table.Group(
    #"Inserted Date",
    {"Team", "Date"},
    {{"Total", each List.Sum([Distance kms]), type nullable number}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Grouped Rows",
    {"Team", "Date"},
    #"Inserted Date",
    {"Team", "Date"},
    "Grouped Rows",
    JoinKind.LeftOuter
  ),
  #"Expanded Grouped Rows" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Grouped Rows",
    {"Origin", "Destination", "Departure Date", "Arrival Date", "Distance kms"},
    {"Origin", "Destination", "Departure Date", "Arrival Date", "Distance kms"}
  )
in
  #"Expanded Grouped Rows"

 

 

DAX measure with a [Date] column added

 

Total2 = 
SUMX(FILTER(ALL('Table'),'Table'[Team]=SELECTEDVALUE('Table'[Team])&&'Table'[Date]=SELECTEDVALUE('Table'[Date])),'Table'[Distance kms])

 

Vera_33_1-1614827798020.png

 

Hello, I think it explains very little about my problem with this table.

The table represents the trips that a fleet of units makes, the problem with the structure of the table is that if I want to show the total distance traveled to a destination, it only shows the distance from the origin to that destination, and the other part of the route puts it in a separate row.

In Excel, this could be solved using formulas to accumulate the distance of a Source-Destination-Source route, and display the total distance (round trip) to the selected destination.

The date solution is good, only that there are trips that start one day and arrive at the destination two or three days after the start of the trip, just like for the return.

Another problem is that because all destinations return to Plant1, they are mixed into a single row.

I need to add the distance T1 traveled from the time it leaves Origin-Plant 1 until it reaches Destination-Plant1.

As to whether it should be a measure or a column, I'm not sure, as this value should be stored for each trip and be able to do calculations about it over time.

Thanks for the support.

Anonymous
Not applicable

Hi, the total is based on Team and Origin & Destination, the date can be diferent between sections of the trip. The total distance is the sum from Plant1 to all destinations are visited and return to Plant1. I need to sum distance from Origin-Plant1 trought sections to Destination-Plant1.

 

Thanks a lot for assistance.

Hi @Syndicate_Admin @Anonymous 

 

It makes more sense now. So need to identify the trip first, then sum up. Do you have more than 1 team? There are other ways.  This is a calculated column, considering more than 1 team

 

Vera_33_0-1614910707159.png

Column = 
VAR CurTeam = 'Table'[Team]
VAR CurDepTime = 'Table'[Departure Date]
VAR CurArrTime = 'Table'[Arrival Date]
VAR StarTime = MAXX(FILTER(ALL('Table'),'Table'[Origin]="Planta1"&&'Table'[Departure Date]<=CurDepTime),'Table'[Departure Date])
VAR EndTime = MINX(FILTER(ALL('Table'),'Table'[Destination]="Planta1"&&'Table'[Arrival Date]>=CurDepTime),'Table'[Arrival Date])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Departure Date]>=StarTime&&'Table'[Arrival Date]<=EndTime&&'Table'[Team]=CurTeam),'Table'[Distance kms])
Anonymous
Not applicable

I test this in my project and works great, so I would like to know if Date function can be work with Date/Time column, because some trips end and start the same Date, and sum sections from another trips. I think that is the final step for solution.

 

Thanks a lot.

Hi @Anonymous 

 

I don't quite follow your question, does it sum up wrong numbers for some trips? Can you provide some sample data of those wrong numbers?

Hello, by mistake use the dates in Date format, now that I reviewed your formula again if you have it as a Date/Time format.

If I correctly apply the formulas in my file, the column does not finish calculating, after 6 hours I kept calculating and had to finish the process from the task manager.

My file has 600K rows, I think it's not very large, but I don't know how to know how long it will take to do the calculations.

Thanks for the support.

Hi @Syndicate_Admin 

Add a custom column in M to determin the same trip first

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHBCsIwEER/RXIudHdWEXvTL+jBW+khaPFS68X/R0IDmzQpSMhtGZjh7cwwmDubxvSzXb7WXdf5bR/T4k7ilqQFgQ+EjjYS0yrxiczY+JzArZGhiTumjXTxOUdoTsIDZ4LyiOdRiT0iOOVBzBOYxPOohFyOmm+f5/RaYyStJ5LESWcNUWtYsqRPSUnJkpYs+yUjV3LB6Kg0epanYHSYOqPH5sLRUWn0/ZL/H338AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Origin = _t, Destination = _t, #"Departure Date" = _t, #"Arrival Date" = _t, #"Distance kms" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Team"}, {{"allrows", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([allrows],"Trip", (IT)=> 
if IT[Origin] = "Planta1" then IT[Index]
else List.Max( Table.SelectRows([allrows],  each [Index] <IT[Index] and [Origin]="Planta1")[Index]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Team", "Origin", "Destination", "Departure Date", "Arrival Date", "Distance kms", "Trip"}, {"Team", "Origin", "Destination", "Departure Date", "Arrival Date", "Distance kms", "Trip"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Distance kms", Int64.Type}})
in
    #"Changed Type"

then a DAX column or a measure in a visual at your choice, here is a DAX column

 

Column = 
VAR CurTrip = [Trip]
RETURN
SUMX(FILTER(Query1,[Trip]=CurTrip),[Distance kms])

Hello, I tried to adapt the formula to my project, changing the "Source" to my original data table, changing this:

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHBCsIwEER/RXIudHdWEXvTL+jBW+khaPFS68X/R0IDmzQpSMhtGZjh7cwwmDubxvSzXb7WXdf5bR/T4k7ilqQFgQ+EjjYS0yrxiczY+JzArZGhiTumjXTxOUdoTsIDZ4LyiOdRiT0iOOVBzBOYxPOohFyOmm+f5/RaYyStJ5LESWcNUWtYsqRPSUnJkpYs+yUjV3LB6Kg0epanYHSYOqPH5sLRUWn0/ZL/H338AQ==", BinaryEncoding.Base64), Compression.Deflate))

By: "TableName" that references my data.

Source = Table.FromRows(#"TableName" ...

But something I have to do wrong, since it doesn't work for me.

Thank you very much for the support.

Hi @Syndicate_Admin 

 

Do you have issue copy the code and paste in Advanced Editor? So you can see the query. If you need to change the Source to your own data, go to Advanced Editor and copy the Source = ...., the whole line and replace the one in my code. You also need to take care of the sort order before adding Index, I assume the data is sorted by Departure Date (datetime). Let me know if you have any questions.

Anonymous
Not applicable

Yes, I have about 43 diferent T.

 

Very very very Thaks for your assistance.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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