Top/Bottom N is not working

[dobregon]

Hi guys,

I have a table in the database with thousands of events, with an identity/unique number for each event, id. In the PowerBI i have a table with the list of events with some metadata related to the event but as i want to have (in order to not upload many rows each time) is to filter the bottom 1000 events created (the last 1000 events).

So, i go to the filter visual ID, filter Botton N, 1000 and in by value i put the ID and it doesnt work and give me the 2000000000 events that i have.

am i doing something wrong? i have tried putting 1 in the bottom N and the result is always de same

[v-lionel-msft]

Hi @dobregon ,

Like this?

Best regards,
Lionel Chen

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

[dobregon]

hi again 

Solved that problem i have found another problem. I have the lsit of events and each event has startdate and enddate and my dataset has a calendar of dates so, depends of the dates that i selected the events need to be filtered by the events that could have in that period.

So, i have the list of events filtered first by a measure like "event active". The problem is that when i tried to apply the top 1000 event (in order to see the first 1000 events) the top 1000 events filter only the first 1000 events not taking into account the event active, it takes into account the whole list of events.

is there any way that that filters are applyied after the applyied that event active?

[v-lionel-msft]

Hi @dobregon ,

The problem with filters is because you have aggregated the wrong way. You could do like this:

Data type 

Filter 

But I don't think you need to do like this. You can directly create a measure and let it return the final result.

Measure = 
CALCULATE(
    MAX(Sheet1[ID]),
    FILTER(
       TOPN( 100, Sheet1, [ID], DESC ),
       Sheet1[StartDate] <= SELECTEDVALUE('Table'[Date]) && Sheet1[EndDate] >= SELECTEDVALUE('Table'[Date])
    )
)

Best regards,
Lionel Chen

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

[v-lionel-msft]

Best regards,
Lionel Chen

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

[dobregon]

thanks for your replies @v-lionel-msft but maybe i dont understand your point or maybe im not explaining very well my exmaple.

i have uploaded an example in the following link PBI file 

As you can see there are 2 tables (actuals and events), the table actuals have the sales for markets for each day and the events are for each market and they have startdate and enddate

The conection betweent tables is the marketid, and the most important table (the mian table) is teh actuals. ok, in the actuals i have 2 measures (date start interval, date end interval) that show yo depends or the days selected the range of dates of your analysis.

then in the event table i have a measure to know if the event is active in the timeperiod or not, so.. if we check the 7th of Jan i want to see the events that are active in that day.

- event starts before that day but are not close

- events start before that day and close that day

- events start that date

- events finish that day, etc etc

For that reason the filtervisual active events is = 1 in order to only show the events active. Ok, so you can see now that could appears a big ammount of events and i want to filter only the TOPN DESC 10 events but eventid but only for the events active in the period (so needs to be dynamic) , in this part is where i have problems

[v-lionel-msft]

Hi @dobregon ,

Has your problem been solved? 

Best regards,
Lionel Chen

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

[dobregon]

not really,

The only idea that i have found is to create a measure that create a new identity values for the active evetns and then filter by top1000 of that measure. the problem is that this option procudes a very low performance so... for now i can't do that

[v-lionel-msft]

Hi @dobregon ,

"So, i have the list of events filtered first by a measure like "event active". The problem is that when i tried to apply the top 1000 event (in order to see the first 1000 events) the top 1000 events filter only the first 1000 events not taking into account the event active, it takes into account the whole list of events."

Try to write all filters in DAX. In other words, directly make the measure return the result you want in the end, do not add "Filters on this visual."

Do you mind sharing your .pbix file? (If the problem remains unresolved.)

Best regards,
Lionel Chen

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

[dobregon]

hi @Greg_Deckler it is a numeric value

hi @v-lionel-msft this is what i did but the visual doesnt filter anything, as you can see in my picture below i have put the eventid column, top N 1 using eventid and nothing happens

The visual is configurated with the eventid only and the option "dont summarize" for that field becasue it is a numeric. The column is numeric 

thanks in advance

[dobregon]

i have created the new column eventid2 as text and try to do the same and this solve the problem so it seems that needs to be text or something.

waiting the confirmation of @v-lionel-msft if i'm right to close the topic.

[Greg_Deckler]

Is ID numeric or text?