cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## how to return dynamic result according to selected value

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

1

2

3

...

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 …
1 ACCEPTED SOLUTION
Community Support

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

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 =
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.

4 REPLIES 4
Community Support

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

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 =
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.

Memorable Member

@oakgold 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
)

BBF
Helper III

the selected value actually should be rank not week😁

Memorable Member

@oakfootballclub @oakgold 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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors