TOP10 Engine - return data for/ ingores second filter

DAX DAX Calculations
1

Dear DNA Team,

I will appreciate your knowledge regarding TOPN formula as today I run into a small issue, which I managed to resolve but I do not understand why I had to apply additional logic.

Top10.pbix (347.0 KB)

As per the attached file top10 formula returns only top 10 customers:

Top 10 = CALCULATE([Sales],

TOPN(10,ALL(Sales[Customer Name Index]),[Sales],DESC),

VALUES(Sales[Customer Name Index]))

image

Sales = IF(ISBLANK(SUMX(Sales,

Sales[Order Quantity] * Sales[Unit Price] )),BLANK(),SUMX(Sales,

Sales[Order Quantity] * Sales[Unit Price] ))

image

The same logic I applied to my live model. The live model is the data from a generic mailbox where I count the number of emails received.

EMEA = IF(ISBLANK(COUNTROWS(EMEAPL)),0,COUNTROWS(EMEAPL))

TOP 10 EMEA = CALCULATE([EMEA],

TOPN(10,ALL(EMEAPL[Email]),[EMEA],DESC),

VALUES(EMEAPL[Email]))

Using the above formula the result I get is:

image

Everything seems fine apart from that TOP10 didn’t return top 10 email senders.

image
image365Ă—636 22.7 KB

In order to return only top10 emails senders I added if statement:

TOP 10 EMEA =

VAR TOP10EMA =CALCULATE([EMEA],

TOPN(10,ALL(EMEAPL[Email]),[EMEA],DESC),

VALUES(EMEAPL[Email]))

Return

IF(TOP10EMA=0,BLANK(),TOP10EMA)

image

My live model pulls the data from 3 separate generic mailboxes then using the below formula I created Unique names of the emails:

Email List = DISTINCT(UNION(DISTINCT(EMEAEXP[Email]),DISTINCT(EMEAPL[Email]),DISTINCT(PLPayables[Email])))

Even though I would create a relationship between support table (unique emails) vs 3 genereic mailboxes and adjust the TOP10 formula to unique email list I would still receive the same result as:
image

Therefore, do you have an explanation why I had to apply if statement?

Thank you,

Mateusz

2

@Matty Because there is duplicate 62 that’s why you get 11 values.

3

@AntrikshSharma I actually get 962 results, 10 results for top 10 and for the rest 952 emails 0.

So do you think this has something to do with the values that are repeating?

I can scroll down the results.

image

@Harsh may I ask for your opinion please :slight_smile:

4

That’s because inside TOPN you are calling a measure which returns 0.

First for these kind of measures always use KEEPFILTERS.

TOP 10 EMEA =
CALCULATE (
    [EMEA],
    KEEPFILTERS (
        TOPN (
            10,
            ALL ( EMEAPL[Email] ),
            [EMEA], DESC
        )
    )
)

and EMEA should just be
EMEA = COUNTROWS ( EMEAPL )

1 Like
5

@AntrikshSharma Thank you for the explanation, unfortunately, I can’t change the EMEA formula as I applied EMEA formula in card visualization too. Sometimes the results returns Blank() (when additional filters are applied on the page) to avoid blanks I round up the formula in If statement.

Thank you for the explanation.

I will use the below as it’s working correctly :slight_smile:
TOP 10 EMEA =

VAR TOP10EMA =CALCULATE([EMEA],

TOPN(10,ALL(EMEAPL[Email]),[EMEA],DESC),

VALUES(EMEAPL[Email]))

Return

IF(TOP10EMA=0,BLANK(),TOP10EMA)

6

@Matty Why don’t you create a separate measure for Card and just add 0 at the end? and keep EMEA simple and call it inside TOPN without needing to check for 0.

1 Like
7

@AntrikshSharma hmm fair point!

Thanks!

1 Like