Forum Discussion

julsr's avatar
julsr
Icon for Continued Contributor rankContinued Contributor
2 years ago
Solved

TopN DAX not working with duplicated values

Hello everyone

 

I'm implementing a measure to identify the TOP5 Values in our dataset, which functions correctly except when dealing with duplicate percentage values. The measure becomes unreliable when encountering tied values.

I attempted a workaround by first calculating TOP10 values and then filtering down to TOP5, but this approach also proves inconsistent. Below you'll find my current implementation and sample data. I'm seeking guidance on how to properly handle scenarios where multiple records share the same percentage value while maintaining accurate TOP5 selection.

 
measure_top5 =
// 1. Get current client
VAR CurrentClient =
  SELECTEDVALUE(table[code])

// 2. Get minimum date with filters
VAR MinDate =
  CALCULATE(
      MIN(table[date]),
      KEEPFILTERS(table[date])
  )

// 3. Get top 10 values to handle ties
VAR Top10Values =
  CALCULATETABLE(
      TOPN(
          10,
          FILTER(
              VALUES(table[code]),
              CALCULATE(
                  table[money_budget] > 400000
              )
          ),
          [diff_measure],
          DESC
      ),
      FILTER(
          ALL(table),
          table[date] = MinDate
      )
  )

// 4. Get actual top 5 from the 10 values
VAR ActualTop5 =
   TOPN(
       5,
       Top10Values,
       [diff_measure],
       DESC
   )

// 5. Check if current client is in top 5
VAR IsCurrentClientInTop5 =
  CALCULATE(
      COUNTROWS(
          FILTER(
              Top10Values,
              table[code] = CurrentClient
          )
      )
  )

RETURN
IF(
  IsCurrentClientInTop5 > 0,
  1,
  0
)

 

 

Code

money_budget

diff_measure

Top 10 measure

147

$676478

23%

1

061

$1702280

9%

0

169

$1344044

9%

1

124

$4087457

5%

1

074

$831424

5%

1

031

$682472

2%

1

068

$8692267

2%

1

036

$396112

 

0

043

$122531

 

0

083

$3330222

0%

1

111

$582983

-2%

1

135

$2328999

-4%

1

067

$675176

-4%

1

118

$9642842

-4%

0

027

$1592563

-7%

0

162

$989317

-8%

0

123

$1235861

-8%

0

151

$1687651

-9%

0

134

$4851934

-9%

0

173

$1116056

-14%

0

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thank you FreemanZ

    Hi, julsr 

    According to your description, I created the following dataset:

    First, I created a date table using the following DAX expression:

    Date = VALUES('Table'[Date])

    I then created a measure using this expression:

    MEASURE =
VAR CurrentClient =
    SELECTEDVALUE ( 'Table'[Code] )
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR Top10Values =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[money_budget] > 400000
                && 'Table'[Date] = MinDate
        ),
        "aa",
            RANK (
                DENSE,
                ALLSELECTED ( 'Table' ),
                ORDERBY ( 'Table'[new_measure_rounded], DESC, 'Table'[Code], DESC )
            )
    )
VAR Top5 =
    SUMMARIZE (
        SELECTCOLUMNS ( FILTER ( Top10Values, [aa] <= 5 ), "Code", 'Table'[Code] ),
        [Code]
    )
RETURN
    IF ( CurrentClient IN Top5, 1, 0 )

    Create a slicer using the Date table, and then place this measure into the Table visual:

     

    When the TopN function cannot meet your needs, you can usually achieve a similar effect through the rank function.

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, julsr 

    This is a new question. You can ask it in a new thread and I believe someone will help you optimize your expression soon.

     

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

    • julsr's avatar
      julsr
      Icon for Continued Contributor rankContinued Contributor
      2 years ago

      Thanks, did something similar, for the [diff_measure] I used a round and then multiplied by 1000 to avoid decimals but it is not working.
      This is the code and below are the data and results

      code_top5 =
      // 1. Get current client
      VAR CurrentClient =
        SELECTEDVALUE(table[code])

      // 2. Get minimum date with filters
      VAR MinDate =
        CALCULATE(
            MIN(table[date]),
            KEEPFILTERS(table[date])
        )


      // 4. Get top 10 values to handle ties
      VAR Top10Values =
        CALCULATETABLE(
            TOPN(
                10,
                FILTER(
                    VALUES(table[code]),
                    CALCULATE(
                        table[money_budget] > 400000
                    )
                ),
                 [new_measure_rounded],
                DESC
            ),
            FILTER(
                ALL(table),
                table[date] = MinDate
            )
        )

      // 5. Get actual top 5 from the 10 values
      VAR ActualTop5 =
         TOPN(
             5,
             Top10Values,
             [new_measure_rounded],
             DESC
         )

      // 6. Check if current client is in top 5
      VAR IsCurrentClientInTop5 =
        CALCULATE(
            COUNTROWS(
                FILTER(
                    ActualTop5,
                    table[code] = CurrentClient
                )
            )
        )

      RETURN
      IF(
        IsCurrentClientInTop5 > 0,
        1,
        0
      )
      --PERCENTAGE_RATE

      Code

      money_budget

      new_measure_rounded

      code_top5

      147

      $676478

      2320

      1

      061

      $1702280

      941

      0

      169

      $1344044

      932

      1

      124

      $4087457

      522

      1

      074

      $831424

      515

      1

      031

      $682472

      222

      1

      068

      $8692267

      219

      0

      036

      $396112

       

      0

      043

      $122531

       

      0

      083

      $3330222

      0%

      0

      111

      $582983

      -222

      0

      135

      $2328999

      -415

      0

      067

      $675176

      -498

      0

      118

      $9642842

      -499

      0

      027

      $1592563

      -743

      0

      162

      $989317

      -898

      0

      123

      $1235861

      -899

      0

      151

      $1687651

      -934

      0

      134

      $4851934

      -976

      0

      173

      $1116056

      -1412

      0

  • julsr's avatar
    julsr
    Icon for Continued Contributor rankContinued Contributor
    2 years ago

    Hello! Just commenting to see if anyone knows how to solve it, thanks 🙏

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you FreemanZ

      Hi, julsr 

      According to your description, I created the following dataset:

      First, I created a date table using the following DAX expression:

      Date = VALUES('Table'[Date])

      I then created a measure using this expression:

      MEASURE =
VAR CurrentClient =
    SELECTEDVALUE ( 'Table'[Code] )
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR Top10Values =
    ADDCOLUMNS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[money_budget] > 400000
                && 'Table'[Date] = MinDate
        ),
        "aa",
            RANK (
                DENSE,
                ALLSELECTED ( 'Table' ),
                ORDERBY ( 'Table'[new_measure_rounded], DESC, 'Table'[Code], DESC )
            )
    )
VAR Top5 =
    SUMMARIZE (
        SELECTCOLUMNS ( FILTER ( Top10Values, [aa] <= 5 ), "Code", 'Table'[Code] ),
        [Code]
    )
RETURN
    IF ( CurrentClient IN Top5, 1, 0 )

      Create a slicer using the Date table, and then place this measure into the Table visual:

       

      When the TopN function cannot meet your needs, you can usually achieve a similar effect through the rank function.

       

       

      How to Get Your Question Answered Quickly

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

      Best Regards

      Jianpeng Li

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • julsr's avatar
        julsr
        Icon for Continued Contributor rankContinued Contributor
        2 years ago

        Awesome, thanks for sharing this valuable information and report! However, I have another problem now: 

        Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 1024 MB, memory limit 1024 MB.

         

        The main problem is that diff_measure is a measure that uses another 3 measures:

        diff_measure =
        IF( [money_budget]>400000,
        DIVIDE(
            [diff_measure_ytd],
            [YTD_budget],
            0
        )
        ,BLANK())

         

        money_budget =
        AVERAGEX(
            FILTER(
                table,
                NOT(ISBLANK(table[budget_total]))  -- this value is repeated the number of rows we have by code so I need to get the average
            ),
            table[budget_total]
        )
         
        diff_measure_ytd =
        [YTD_TotalSales]-[YTD_budget]
         
        YTD_budget =
        VAR CurrentDate = MAX(table[date])
        VAR YearStartDate = DATE(YEAR(CurrentDate), 1, 1)
        RETURN
        CALCULATE(
            SUMX(
                SUMMARIZE(
                    FILTER(
                        table,  
                        NOT(ISBLANK(table[budget_total]))  -- Exclude BLANK values
                    ),
                    table[code],  
                    table[month], 
                    "MonthlyBudget", AVERAGE(table[total_budget])  -- Calculate distinct budget for each code by month
                ),
                [MonthlyBudget]
            ),
            FILTER(
                ALL(table[date]),  
                table[date] >= YearStartDate &&  -- From the start of the year
                table[date] <= CurrentDate &&    -- Until the current date (this month)
                YEAR(table[date]) = YEAR(CurrentDate)  -- Ensure it's within the same year
            )
        )
         
         YTD_TotalSales=
        VAR CurrentDate = MAX(table[date])
        RETURN
        CALCULATE(
            [sales_column],
            FILTER(
                ALL(table),
                table[date] <= CurrentDate &&
                YEAR(table[date]) = YEAR(CurrentDate)
            ),
            VALUES(table[code])
        )


        Any idea on how I could improve it? The problem is that the data is historic, we have a lot of information, and as it needs to be dynamic, the coding part is being done in PowerBI
         
        thanks