TOP 5 BLOCKS WITH MOST Number of IMV incidents

[Vandithak]

Hi Support,

I need to calculate Top 5 blocks based on IMV Incidents. I can see results ae correct as per DB , but the problem is having Nth posistion is having same number. I have Used TOPN function, but there is a limitaion that

• If there is a tie, in Order_By values, at the N-th row of the table, then all tied rows are returned. .... In my situation 5th postion is having (16) tied numbers 
  

  
  I have used formulas like 
  
  

  Top5Check =

  VAR Top5Table =

      TOPN(

          5,

          ADDCOLUMNS(

              VALUES(Block[Block_ID]),

              "IncidentCount",

              CALCULATE(

                  COUNTROWS(factIncidentLog),

                  IncidentType[IncidentType] = 100

              )

          ),

          [IncidentCount],

          DESC,

          Block[Block_ID]

      )

  VAR CurrentBlockID = SELECTEDVALUE(Block[Block_ID])

  
  

  RETURN

      IF(

          CurrentBlockID IN SELECTCOLUMNS(Top5Table, "BlockID", Block[Block_ID]),

          CALCULATE(

              COUNTROWS(factIncidentLog),

              IncidentType[IncidentType] = 100

          ),

          BLANK()

      )
  
  2nd formula
  ----------------
  

  IncidentCount_100 =

  CALCULATE(

      COUNTROWS(factIncidentLog),

      IncidentType[IncidentType] = 100

  )

  BlockRank_1008 =

  RANKX(

      ALL(Block[Block_ID]),

      [IncidentCount_100],

      ,

      DESC,

      Skip/Dense

  )
  

  Top5IncidentCount =

  VAR a = [BlockRank_100]

  RETURN

      IF(a <= 5, [IncidentCount_100], BLANK())
  
  But still i can see tied blocks. Is ther any other option to replace this . As i need to see only top 5 blocks even if its tied number also. I need dax to get only top5 
  
  

  
  
  
  

[v-hashadapu]

Hi @Vandithak , We are closing this thread as we haven't heard from you in a while, according to our follow-up policy. If you have any more questions, please start a new thread on the Microsoft Fabric Community Forum. We will be happy to assist you! Thank you for being part of the community!

[v-hashadapu]

Hi @Vandithak , it's been a while since we last heard from you. We are ready to assist you with resolving the issue, but we need the necessary details from you. Kindly share the information required so we can better understand and address your issue.

[v-hashadapu]

Hi @Vandithak ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[v-hashadapu · Accepted solution]

Hi @Vandithak , Thank you for reaching out to the Microsoft Community Forum.

Please try below:

IncidentCount_100 =

CALCULATE(

    COUNTROWS(factIncidentLog),

    IncidentType[IncidentType] = 100

)

RankWithTiebreaker =

RANKX(

    ALL(Block[Block_ID]),

    [IncidentCount_100] & "-" & Block[Block_ID],

    ,

    DESC,

    DENSE

)

Top5Blocks =

IF(

    [RankWithTiebreaker] <= 5,

    [IncidentCount_100],

    BLANK()

)

If it doesn’t help, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[Vandithak]

Hi @v-hashadapu Thanks for providing this. Results are correct as per db, but when i pulled Block id and above formula in bar chart i can see 6 items instead of top 5 . It should display top 5 Blocks onlyeven though the number ties. As per below exmaple 2113 block wont show 

[v-hashadapu]

Hi @Vandithak , Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[Vandithak]

Hello @v-hashadapu Modified DAX also not giving correct results.

[Vandithak]

Hi @johnt75 , Thanks for your reply. But the provided dax is not giving Correct results. I just modified Dax provided by you as below ( ORDERBY function not listed in Dax)
Ranking :=
RANKX(
ALLSELECTED(Block[Block_ID]),
CALCULATE(
COUNTROWS(factIncidentLog),
IncidentType[IncidentType] = 100
),
,
DESC,
DENSE
)

[johnt75]

RANKX isn't going to work, you can only specify a single sort order metric. You need to use RANK instead.

[v-hashadapu]

Hi @Vandithak , Thanks for the update. Can you please confirm if your modified DAX solved the issue. If it did, please mark your reply with the modified DAX "Accept as Solution", so others with similar queries may find it easily. If not, please share the details. Thank you.

[johnt75]

The RANK function allows you to use multiple criteria for ranking, so you could use e.g. the Block ID ranked alphanumerically to split ties.

Ranking =
RANK (
    ALLSELECTED ( Block[Block_ID] ),
    ORDERBY (
        CALCULATE ( COUNTROWS ( factIncidentLog ), IncidentType[IncidentType] = 100 ), DESC,
        Block[Block_ID], ASC
    )
)

Add this measure as a Top N filter on the visuals you want to limit.