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]
    )
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]
    )