cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 IsAs 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?

 

ToBeToBe

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Showing only required rows in Matrix based on Measure Value

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.

View solution in original post

11 REPLIES 11
Highlighted
Anonymous
Not applicable

Re: Showing only required rows in Matrix based on Measure Value

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. 

Highlighted
Helper I
Helper I

Re: Showing only required rows in Matrix based on Measure Value

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.

Highlighted
Solution Sage
Solution Sage

Re: Showing only required rows in Matrix based on Measure Value

 

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])

 

 

 

 

Highlighted
Helper I
Helper I

Re: Showing only required rows in Matrix based on Measure Value

Hi,

In this scenario, both columns are coming from different tables. Also I need to show only those rows which have minimum positive value and should not be 0. For e.g. out of measure values (0,5,234) 5 value measure row should only come for Item and Grade.
Highlighted
Solution Sage
Solution Sage

Re: Showing only required rows in Matrix based on Measure Value

Basic method should still work. Create a measure or variable to calculate the MAX. Then use that to block all results less than the max. Can you share some sample data?
Highlighted
Helper I
Helper I

Re: Showing only required rows in Matrix based on Measure Value

Hi Seward,

 

Thanks for your help!

1) When I use MAX(Measure) this formula gives me error that MAX only accept a column reference. In my case "Measure" field is not any column or cal column, it is a measure I created in report.

 

2) Also, ITEM field is coming from different table, Grade is coming from different table and Measure is calculated on some other table. All three tables are connect though.

 

I could implement ranking column using RANKX as:

Rankx = RANKX(ALLSELECTED(Table1[LOT_GRADE_CD]),[RankTest],,ASC)

RankTest = CALCULATE([Measure])

Results are: 

Capture.PNG

 

I need to filter this Matrix visual for Rankx column = 1.

 

I tried doing this using this thrugh visual filters but it filters complete visual not on the basis of Measure field. Do you know how this can be done.

Or

If there is any other way?

 

Thanks

 

Highlighted
Solution Sage
Solution Sage

Re: Showing only required rows in Matrix based on Measure Value

Should work if you max a [MAX Measure] measure that calculates the MAX.  but using your solution you can update your formula for [Measure] or write a new [Display Measure] to block display if [Rankx]>1

 

Display Measure = IF([Rankx]>1,[Measure])

 

and then use this new measure in your Matrix. Tip - if you don't want to dipslay the measure then do a Table and then use a Visual filter usign Rankx measure werhe  Rankx=1

Highlighted
Helper I
Helper I

Re: Showing only required rows in Matrix based on Measure Value

Sorry Seward but the solution you have given didn't help me.

 

If after creating Display Measure, when I drag it in table/matrix visualization it shows me this result:

Capture.PNG

 

Display Measure = IF([Rankx]=1,[Measure])

 

Changed it to match my requirement. If apply filter on "Display Measure" like is not blank it removes all rows.

 

 

Highlighted
Solution Sage
Solution Sage

Re: Showing only required rows in Matrix based on Measure Value

You need to remove MEASURE and Rankx from the table. The idea of IF blocking is it returns NULL if condition is not met and the measure returns NUL.   

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors