Forum Discussion

ohqpi's avatar
ohqpi
New Member
1 year ago
Solved

Create unique IDs to group same values by date

Hi all,    I am using a data set that has been added using import mode, so I am unable to use the Advance Editor to manipulate the data. Basically, my data set has some IDs that have varying volume...
Need Help
  • speedramps's avatar
    speedramps
    1 year ago

    Click here to download a solution from Onedrive
    Click here 

     

    How it works ....

    Create a table with your test data

     

    Group it

     

     

    Group it again

    Add a custom column

    Remove all columns except the custom column

     

    Expand the column

     

    Add a customer column

     

    Remove the unneeded columns and reorder  columns

     

    Please click [thumbs up]  because I hace tried really hard.

    And click [accept solution] it it works  .... it clearly does work.
    Thank you ðŸ˜€

     

     

     

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi ohqpi ,

Yes, you can approach this in DAX, but it requires a few calculated columns and measures since DAX does not offer row-by-row transformation like Power Query’s Advanced Editor. Here’s a way you can achieve similar results:

Step 1: Create a grouping key

First, create a calculated column to identify groups where the same ID has the same Volume across consecutive dates.
You’ll need to compare each row with the previous row for the same ID to detect where a new group starts.

DAX
 
GroupKey =
VAR PrevVolume =
    CALCULATE(
        MAX('YourTable'[Volume]),
        FILTER(
            'YourTable',
            'YourTable'[ID] = EARLIER('YourTable'[ID]) &&
            'YourTable'[Date] = EARLIER('YourTable'[Date]) - 1
        )
    )
VAR NewGroup =
    IF(
        'YourTable'[Volume] <> PrevVolume || ISBLANK(PrevVolume),
        1, 0
    )
VAR GroupNumber =
    CALCULATE(
        SUMX(
            FILTER(
                'YourTable',
                'YourTable'[ID] = EARLIER('YourTable'[ID]) &&
                'YourTable'[Date] <= EARLIER('YourTable'[Date])
            ),
            IF(
                'YourTable'[Volume] <> 
                CALCULATE(
                    MAX('YourTable'[Volume]),
                    FILTER(
                        'YourTable',
                        'YourTable'[ID] = EARLIER('YourTable'[ID]) &&
                        'YourTable'[Date] = EARLIER('YourTable'[Date]) - 1
                    )
                ) || ISBLANK(
                    CALCULATE(
                        MAX('YourTable'[Volume]),
                        FILTER(
                            'YourTable',
                            'YourTable'[ID] = EARLIER('YourTable'[ID]) &&
                            'YourTable'[Date] = EARLIER('YourTable'[Date]) - 1
                        )
                    )
                ),
                1, 0
            )
        )
    )
RETURN
    'YourTable'[ID] & "-" & GroupNumber

Step 2: Calculate Start Date, End Date, and Sum of Volume

Now you can aggregate by this GroupKey:

  • Start Date: MIN(Date) for each GroupKey
  • End Date: MAX(Date) for each GroupKey
  • Sum of Volume: SUM(Volume) for each GroupKey

You can do this either in a summary table or via measures in your visual.

Example Summary Table:

DAX
 
SummaryTable =
SUMMARIZE(
    'YourTable',
    [GroupKey],
    "ID", MAX('YourTable'[ID]),
    "Start Date", MIN('YourTable'[Date]),
    "End Date", MAX('YourTable'[Date]),
    "Sum of Volume", SUM('YourTable'[Volume])
)

Note:

  • You may need to adjust the date comparison logic if your dates are not consecutive or are not integers.
  • If your [Date] column is text, you’ll need to convert it to a Date type.
  • This approach works in import mode and is fully DAX-based.

Let me know if you need help adapting the logic to your exact table or date format!
translation and formatting supported by AI