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
leopoldvili_1
Helper I
Helper I

TopN and all other on graph chart, with other slicers on.

Hi, I would like to visualize topN and all others on a graph chart.  I have the following table: 

leopoldvili_1_0-1712502192559.png

and I am using a formula to calculate ViolationsNTop and other:

 

ViolationsNTop = var Rankviolationbycount =RANKX(ALL('Table'),'Table'[Counts],,Desc)
return
IF(Rankviolationbycount<=4,'Table'[Error],"Other")

 

leopoldvili_1_0-1712501475089.png 

Howewer, It works partly, but when I select filter ID: it does not work as expected, it should show the top 4 violations+others for selected filter AA1. 
Can anyone help here?
Thank you.
2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713059983406.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

You are welcome.  PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713334465091.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713059983406.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Wow, Thank you very much! It works. Only I would love to have "others"as the last values in the graph? is it easily possible? 

 

You are welcome.  PBI file attached.

Hope this helps.

Ashish_Mathur_0-1713334465091.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you very much! it is exactly, what I wanted! Wow, thank you one more time.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yifanw-msft
Community Support
Community Support

Thank you @lbendlin  for your prompt response.

Hi @leopoldvili_1 ,
Based on the information you have provided, you may consider following the steps below to resolve your issue:
1. Add two new columns.

AA1 = 
VAR _RANK =
    RANKX (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Counts],
        ,
        DESC
    )
RETURN
    IF (
        _RANK <= 4
            && 'Table'[ID] = "AA1",
        'Table'[Error],
        IF ( 'Table'[ID] = "AA2", BLANK (), "Other" )
    )
AA2 = 
VAR _RANK =
    RANKX (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Counts],
        ,
        DESC
    )
RETURN
    IF (
        _RANK <= 4
            && 'Table'[ID] = "AA2",
        'Table'[Error],
        IF ( 'Table'[ID] = "AA1", BLANK (), "Other" )
    )


2. Create a new table with three options.

vyifanwmsft_7-1712651781605.png


3. Create a field parameter and establish a relationship with the new table, add Parameter in Y-axis.

vyifanwmsft_1-1712651119596.png

vyifanwmsft_2-1712651181987.png

vyifanwmsft_3-1712651228381.png

vyifanwmsft_9-1712652582890.png


4. Add a MEASURE to the visual corresponding to the options for filtering, add Table2 AA in slicer.

SELECTED = 
IF (
    SELECTEDVALUE ( Table2[AA] ) = "AA1"
        && SELECTEDVALUE ( 'Table'[AA1] ) <> BLANK (),
    1,
    IF (
        SELECTEDVALUE ( Table2[AA] ) = "AA2"
            && SELECTEDVALUE ( 'Table'[AA2] ) <> BLANK (),
        1,
        IF ( SELECTEDVALUE ( Table2[AA] ) = "ViolationsNTop", 1, 0 )
    )
)

vyifanwmsft_11-1712652728070.png

 

vyifanwmsft_8-1712652380705.png

 

Final output:

vyifanwmsft_10-1712652609983.png

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

 

Best Regards,

Ada Wang

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

Thank you very much, for your so detailed explanation. It works if ID we have only 2, but in the real project I  might have several hundreds different IDs. I will try to find based on your provided Dax  solution, to change logic slighly. Thank you very much one more time.

 

lbendlin
Super User
Super User

Change ALL() to ALLSELECTED()

Hi, thank you for you reply, Howewer, even after implementing " AllSelected", and when I select filter "AA1", it is not showing  grouped errors based on Id. [Example of output in the screenshot} 

leopoldvili_1_0-1712527840372.png

 

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.