Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

12 REPLIES 12
nxa15428
New Member

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

Anonymous
Not applicable

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
Not applicable

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.

 

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

 

 

 

 

Anonymous
Not applicable

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.

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?
Anonymous
Not applicable

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

 

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

Anonymous
Not applicable

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.

 

 

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.   

Anonymous
Not applicable

Sorry this solution is not helping me. Can you give some other solution?
Can anybody else help me getting this implemented?

Thanks
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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