topic Re: how to return dynamic result according to selected value in DAX Commands and Tips

how to return dynamic result according to selected value

Anonymous — Tue, 09 Jul 2024 10:05:44 GMT

Hi experts, I want to create a table visual that returns the selected top n table

here the sample data:

week	id	value
w1	E01	163
w1	E01	145
w1	E02	94
w1	E02	35
w1	E03	123
w1	E04	8
w1	E06	177
w1	E07	38
w2	E01	103
w2	E02	180
w2	E03	31
w2	E04	57
w2	E06	88
w2	E07	179
w2	E08	69
w3	E02	177
w3	E03	176
w3	E04	62
w3	E06	92

and the expecte result is rank of id based on amount of each week, and returns a table with week, id, sum of amount and top n of rank, notice that the rank is based on the slicer of the value, the slicer is another table with no relation with the data above:

topn

...

10,

for example, when topn slicer is 3, the expected table is:

week	id	sum	rank
w1	E01	300	2
w1	E02	200	3
w1	E06	700	1
w2	E02	100	1
w2	E06	20	3
w2	E07	50	2
w3
…

when topn slicer is 4, then the table is:

week	id	sum	rank
w1	E01	300	2
w1	E02	200	3
w1	E06	700	1
w1	E07	166	4
w2	E02	100	1
w2	E06	20	3
w2	E07	50	2
w2	E01	10	4
w3
…

Re: how to return dynamic result according to selected value

BeaBF — Tue, 09 Jul 2024 11:05:19 GMT

Anonymous Hi! Here the measure for rank:

Rank =

VAR CurrentWeek = SELECTEDVALUE('Table'[week])

RETURN

RANKX(

FILTER(

ALL('Table'),

'Table'[week] = CurrentWeek

), CALCULATE(SUM('Table'[value])),

DESC, Skip

)

if it's ok please accept my answer as a solution!

BBF

Re: how to return dynamic result according to selected value

oakfootballclub — Tue, 09 Jul 2024 13:08:35 GMT

the selected value actually should be rank not week😁

Re: how to return dynamic result according to selected value

BeaBF — Tue, 09 Jul 2024 13:20:32 GMT

oakfootballclub Anonymous the correct measure:

Rank =

VAR CurrentWeek = SELECTEDVALUE('Table'[week])

VAR Calc =

RANKX(

FILTER(

ALL('Table'),

'Table'[week] = CurrentWeek

), CALCULATE(SUM('Table'[value])),

DESC, Skip

)

RETURN IF(Calc <= SELECTEDVALUE(TOPN_TABLE[topn]), Calc)

now if you select from the slicer 4 it returns:

if you select 3, it returns:

The SELECTEDVALUE on week is used only to group by Week in the rank calc.

BBF

Re: how to return dynamic result according to selected value

Anonymous — Thu, 25 Jul 2024 01:44:59 GMT

Hi, BeaBF
I am in favor of your method. At the same time I would like to share another solution.

Hi, Anonymous oakfootballclub

I am glad to help you.

According to your description, you want to how to return dynamic result according to selected value?

If I understand you correctly, then you can refer to my solution.

1. Create a new table for handsome selection of top n.

2. New table, calculate the value grouped according to week and id.

Table2 = ADDCOLUMNS ( SUMMARIZE ( Table_, Table_[week], Table_[id], "TotalValue", SUM ( Table_[value] ) ), "TotalValueByWeekAndID", [TotalValue] )

3. New Column, according to the value calculated in the first step of the descending order, to get the top three values.

RankWithinWeekAndID = RANKX ( FILTER ( ALL ( 'Table2' ), 'Table2'[week] = EARLIER ( 'Table2'[week] ) ), 'Table2'[TotalValueByWeekAndID], , DESC, DENSE )

4.New Measure, and then use the Measure to filter the visual.

TopNFrequency = VAR CurrentID = SELECTEDVALUE ( Table2[id] ) Var Top_N = SELECTEDVALUE('Top_Number'[TopN]) RETURN CALCULATE ( COUNTROWS ( FILTER ( Table2, Table2[RankWithinWeekAndID] <= Top_N && Table2[id] = CurrentID ) ) )

5.Change slicer to single selection:

6.Here is the result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,
Fen Ling,

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