topic Re: TOPN with variable for N_Value in DAX Commands and Tips

TOPN with variable for N_Value

NW953 — Tue, 13 Aug 2024 16:49:48 GMT

I have software usage data and depending of the software type we want to assign the TOPN users to discounted seats.... The number of discounted seats we have varies by software type... At the end I have a table that shows all of the pricing options and we want to select the cheapest route per user and come up with a total budget...

I am currently using TOPN to create a top number of users in software reports and the function works well if I enter a stactic value for the N_value, however if I try to use a function in the place the measure does not work even htough I have confirmed the right number is getting passed with the function... Is this a bug/ limitation?

if I replace the number 69 below with any other method of getting that number the measure stops working... I need N_value to be dynamic... what am I doing wrong here?

for example I want to replace 69 here with min(discounted seats)

My measure:

Forecast PDM Discount Seat Cost =

var PDM_Disc_Users =

TOPN(69,

ALLSELECTED(Usage[email]),

[PDM Usage],

DESC)

RETURN

CALCULATE(

sumx(values(Usage[email]), min(Usage[PDM Discount Seat Cost])),

filter(VALUES(Usage[email]), Usage[email] IN PDM_Disc_Users)

)

Re: TOPN with variable for N_Value

SachinNandanwar — Tue, 13 Aug 2024 17:11:27 GMT

Try setting min(discounted seats) to a variable and then use it instead of 69.

Re: TOPN with variable for N_Value

NW953 — Tue, 13 Aug 2024 17:17:11 GMT

I have tried that as well, no luck

Re: TOPN with variable for N_Value

SachinNandanwar — Tue, 13 Aug 2024 17:19:34 GMT

What error do you get ?

Re: TOPN with variable for N_Value

NW953 — Tue, 13 Aug 2024 17:28:44 GMT

I just don't get any results... version 2 uses the function, version 1 is hard coded

Re: TOPN with variable for N_Value

ahadkarimi — Tue, 13 Aug 2024 19:16:35 GMT

Hey NW953, try this one and let me know if there is any problem.

Forecast PDM Discount Seat Cost = var N_Value = MIN(Usage[Discounted Seats]) var PDM_Disc_Users = TOPN(N_Value,ALLSELECTED(Usage[email]), [PDM Usage],DESC) RETURN CALCULATE(sumx( values(Usage[email]), min(Usage[PDM Discount Seat Cost])), filter(VALUES(Usage[email]),Usage[email] IN PDM_Disc_Users))