Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Showing only required rows in Matrix based on Measure Value

Hi Experts,

 

I need to implement one logic in Matrix visual in Power BI. As shown below, I have a matrix visual where "ITEM" and "Grade" are fields coming from columns while there is one measure created in report which brings these values for below shown visual.

 

 As Is

I need my visual to only show one row per ITEM value with Grade value which has minimum measure value like below. No Measure column should be shown in final visual. It has to be matrix. Can we achieve this somehow?

 

ToBe

Above one (ToBe) is for refrence only. Any help would be appreciated

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Seward12533,

     

    I could resolve this issue by creating a calculated column to bring GRADE value in the table using Related function.

     

    I used this formula for getting it implemented using RANKX:

     

     

    Test = 
CALCULATE (
    [MEASURE],
    FILTER (
        INVENTORY,
        NOT ( ISBLANK ( INVENTORY[GRADE] ) )
            && [MEASURE] > 0
    )
)

     Then using RANKX:

    Rank = 
IF (
    NOT ( ISBLANK ( [Test] ) ),
    RANKX (
        FILTER ( ALL ( INVENTORY[GRADE] ), [Test] ),
        [Test],
        ,
        ASC,
        DENSE
    ),
    BLANK ()
)

    The issue was happening due to duplicates and blank values. It resolved the issue.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    You could create a measure like this:

    Grade Measure = MIN(Table[Grade])

    Then use a table visual, drag Item column and Grade Measure into it. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi,

       

      Thanks for the reply.

      This doesn't solve the problem as Grade field is showing same values in content of Measure field. Can it be done in some other way?

       

      Using RANKX function which starts with 1 value always for every ITEM. Then filter RANX column =1. I am trying to implement this. If somebody has already used something like this, please share.

      • Seward12533's avatar
        Seward12533
        Solution Sage
        8 years ago

         

        Could try a measure like this to block dispalying everythign but largest

         

         

        Display Measure = VAR MAX_Grade = CALCULATE(MAX([MEASURE]),ALL(table[RANK])) RETURN IF([MEASURE]=MAX_Grade,[MEASURE])

         

         

         

         

  • nxa15428's avatar
    nxa15428
    Regular Visitor
    2 years ago

    Thanks for sharing, i add two meaure below can resolve the issue:

    ===== [Rank] measure
    Rank =
    IF (
    NOT ( ISBLANK ( [Test] ) ),
    RANKX (
    FILTER ( ALL ( INVENTORY[GRADE] ), [Test] ),
    [Test],
    ,
    ASC,
    DENSE
    ),
    BLANK ()
    )

    ==== [Rank Of Grade] measure

    Rank Of Grade =
    CALCULATE (MIN(INVENTORY[GRADE]),
    FILTER ( ALL ( INVENTORY[GRADE] ),[Rank]=1 )
    )