Forum Discussion

technologyLMM's avatar
technologyLMM
Frequent Visitor
3 years ago
Solved

Create new table based on row data

Hi all,   i need to create a table from an initial table, but some calculations shall be performed for several rows/columns.  Please note below the example and advise if this is feasible to be don...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Thank you for your message.

    Please check the attahced file, and the below DAX formula.

     

    New TableV2 = 
VAR _start =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( DataV2, DataV2[Start] = "yes" ),
            DataV2[Date],
            DataV2[Car ID]
        ),
        "@Start Date", DataV2[Date],
        "@car", DataV2[Car ID]
    )
VAR _startindex =
    ADDCOLUMNS (
        _start,
        "@index",
            COUNTROWS (
                FILTER (
                    _start,
                    [@Start Date] <= EARLIER ( [@Start Date] )
                        && [@car] = EARLIER ( [@car] )
                )
            )
    )
VAR _end =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( DataV2, DataV2[End] = "yes" ),
            DataV2[Date],
            DataV2[Car ID]
        ),
        "@End Date", DataV2[Date],
        "@car2", DataV2[Car ID]
    )
VAR _endindex =
    ADDCOLUMNS (
        _end,
        "@index2",
            COUNTROWS (
                FILTER (
                    _end,
                    [@End Date] <= EARLIER ( [@End Date] )
                        && [@car2] = EARLIER ( [@car2] )
                )
            )
    )
VAR _position =
    ADDCOLUMNS (
        FILTER (
            GENERATE ( _startindex, _endindex ),
            [@index] = [@index2]
                && [@car] = [@car2]
        ),
        "@startposition",
            MAXX (
                FILTER (
                    DataV2,
                    DataV2[Date] = EARLIER ( [@Start Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Position]
            ),
        "@endposition",
            MAXX (
                FILTER (
                    DataV2,
                    DataV2[Date] = EARLIER ( [@End Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Position]
            )
    )
VAR _total =
    ADDCOLUMNS (
        _position,
        "@total",
            SUMX (
                FILTER (
                    DataV2,
                    DataV2[Date] >= EARLIER ( [@Start Date] )
                        && DataV2[Date] <= EARLIER ( [@End Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Fuel] + DataV2[Extra Fuel]
            )
    )
RETURN
    SUMMARIZE (
        _total,
        [@Start Date],
        [@End Date],
        [@startposition],
        [@endposition],
        [@total]
    )
technologyLMM's avatar
technologyLMM
Frequent Visitor
3 years ago

Dear Jihwan_Kim you are absolutely perfect!!

Many many thanks for your valuable contribution and assistance.

It it the first time working with VAR, so your support is much appreciated.

 

I have two additional columns that i need to implement in the current project and may affect the prepared code.

1) column "Car ID".

2) "extra fuel" column, which shall be also summarized in the final table (Fuel + Extra Fuel).

 

Initial Table:

DateCar ID Start End Position Fuel Extra Fuel
18/10/2022 A yes -AAA 10 0
19/10/2022  A - -BBB 13 0
20/10/2022 A - yesCCC 11 1
21/10/2022 A yes -DDD 12 0
22/10/2022 A - -EEE 12 2
23/10/2022 A - -ZZZ 13 3
24/10/2022 A - yesAAA 10 1
25/10/2022 A yes -BBB 10 0
26/10/2022 A - -AAA 13 0
27/10/2022 A - -BBB 11 1
28/10/2022 A - -CCC 12 0
29/10/2022 A - -DDD 12 2
30/10/2022 A - -EEE 12 2
31/10/2022 A - -ZZZ 10 1
01/11/2022 A - yesKKK 10 1
18/10/2022 B yes -AAA30 0
19/10/2022 B - -BBB42 0
20/10/2022 B - -CCC20 1
21/10/2022 B - -DDD304
22/10/2022 B - -EEE435
23/10/2022 B - -ZZZ232
24/10/2022 B - yesLLL325
25/10/2022 B yes -AAA32 0
26/10/2022 B - yesBBB21 0
21/10/2022 C yes -AAA204
22/10/2022 C - -BBB215
23/10/2022 C - yesCCC212

 

The new table shall be like this

Start Date End Date Car ID Start Position End Position Total Fuel (sum)
18/10/2022 20/10/2022 A AAA CCC 35
21/10/2022 24/10/2022 A DDD AAA 53
25/10/2022 01/11/2022 A BBB KKK 85
18/10/2022 24/10/2022 B AAA LLL 236
25/10/2022 26/10/2022 B AAA BBB 53
21/10/2022 23/10/2022 C AAA CCC 73

 

Could you please advise how shall your code be amended to depict the final table?

Thank you in advance for your extreme support.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Sorry that I cannot understand how CAR column looks like and how it plays in the table.

    Please provide the full range of your sample, instead of showing CAR = A only.

    • technologyLMM's avatar
      technologyLMM
      Frequent Visitor
      3 years ago

      I have updated the table in my above message to include additional data, thus becoming more clear.

      Thank you in advance for your great suppport

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        Hi,

        Thank you for your message.

        Please check the attahced file, and the below DAX formula.

         

        New TableV2 = 
VAR _start =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( DataV2, DataV2[Start] = "yes" ),
            DataV2[Date],
            DataV2[Car ID]
        ),
        "@Start Date", DataV2[Date],
        "@car", DataV2[Car ID]
    )
VAR _startindex =
    ADDCOLUMNS (
        _start,
        "@index",
            COUNTROWS (
                FILTER (
                    _start,
                    [@Start Date] <= EARLIER ( [@Start Date] )
                        && [@car] = EARLIER ( [@car] )
                )
            )
    )
VAR _end =
    SELECTCOLUMNS (
        SUMMARIZE (
            FILTER ( DataV2, DataV2[End] = "yes" ),
            DataV2[Date],
            DataV2[Car ID]
        ),
        "@End Date", DataV2[Date],
        "@car2", DataV2[Car ID]
    )
VAR _endindex =
    ADDCOLUMNS (
        _end,
        "@index2",
            COUNTROWS (
                FILTER (
                    _end,
                    [@End Date] <= EARLIER ( [@End Date] )
                        && [@car2] = EARLIER ( [@car2] )
                )
            )
    )
VAR _position =
    ADDCOLUMNS (
        FILTER (
            GENERATE ( _startindex, _endindex ),
            [@index] = [@index2]
                && [@car] = [@car2]
        ),
        "@startposition",
            MAXX (
                FILTER (
                    DataV2,
                    DataV2[Date] = EARLIER ( [@Start Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Position]
            ),
        "@endposition",
            MAXX (
                FILTER (
                    DataV2,
                    DataV2[Date] = EARLIER ( [@End Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Position]
            )
    )
VAR _total =
    ADDCOLUMNS (
        _position,
        "@total",
            SUMX (
                FILTER (
                    DataV2,
                    DataV2[Date] >= EARLIER ( [@Start Date] )
                        && DataV2[Date] <= EARLIER ( [@End Date] )
                        && DataV2[Car ID] = EARLIER ( [@car] )
                ),
                DataV2[Fuel] + DataV2[Extra Fuel]
            )
    )
RETURN
    SUMMARIZE (
        _total,
        [@Start Date],
        [@End Date],
        [@startposition],
        [@endposition],
        [@total]
    )