Skip to main content
cancel
Showing results for 
Search instead 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

Reply
oakgold
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
v-fenling-msft
Community Support
Community Support

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

 

Hi, @oakgold @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.

vfenlingmsft_0-1720573891074.png

 

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:

vfenlingmsft_1-1720573891079.png

6.Here is the result: 

vfenlingmsft_0-1721871825627.png

 

 

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.

View solution in original post

4 REPLIES 4
v-fenling-msft
Community Support
Community Support

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

 

Hi, @oakgold @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.

vfenlingmsft_0-1720573891074.png

 

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:

vfenlingmsft_1-1720573891079.png

6.Here is the result: 

vfenlingmsft_0-1721871825627.png

 

 

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.

BeaBF
Memorable Member
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
    )

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

the selected value actually should be rank not week😁

@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:
BeaBF_0-1720531125085.png

if you select 3, it returns:

BeaBF_1-1720531152199.png

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

 

BBF

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

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