TopN DAX not working with duplicated values

[julsr]

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

[Anonymous · Accepted solution]

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]

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

[Anonymous · Accepted solution]

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]

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

[Anonymous · Accepted solution]

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.

[FreemanZ]

hi @julsr ,

check this:

https://community.fabric.microsoft.com/t5/Desktop/Top-N-displaying-more-than-N-when-duplicate-values...

[julsr]

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