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.
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?
Above one (ToBe) is for refrence only. Any help would be appreciated
Solved! Go to Solution.
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.
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])
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])
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.
If there is any other way?
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
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:
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.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!