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

Getting Dual values in Matrix table instead of one. Also trying to get Min Value from the dual value

Hello Team
I am new to PowerBi and wanted to understand how to restrict the data to it's minimum value in Matrix table. I am getting dual values under a matrix table. Could somebody help.The value is from a custom calcualted column. So the logic behind is 

  • If KPI = "A"     then get      'A' Column Score
  • If KPI = "B"     then get       'B' ColumnScore
  • If KPI = "C"     then get      'C' Column Score

               If KPI = "D" then get minimum of 'D' Column Score.

 

The problem lies  here in getting the minimum score as A,B,C has no issues.
The M query formula used to get the custom column is 

= Table.AddColumn(#"Added Custom", "Custom Score QED = ", each if [KPI] = "TMLNS" then [TGC] else if [KPI] = "CMPLS" then [CGC] else if [KPI] = "AVLBT" then [AGC] else if [KPI] = "OVERALL" then [OVERALL_SC] else null)

 

I am able to plot all the values in a single row, but when it comes to D, I am getting dual values and not minimum score.

 

So here A, B, C refers to AVLBT, CMPLS, TMLNS. D refers to "OVERALL" and facing issue with values of overall plotted in the matrix table.

I have tried using List.Min() but yet no results.


So any approach to solve this would be helpfull as I am really stuck 😞 

 Untitled.png

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

Since it is more difficult to do in power query, you could try to create measures in power pivot.

When you open the power bi desktop, instead going to "Edit Queries", just from "Home", you can select "New Measures" or "New Column".

Measures as below

MIN OF D = CALCULATE(MIN([D]),ALLEXCEPT(Sheet4,Sheet4[cgranty_ode]))

Custom Score QED = SWITCH(TRUE(),MAX([KPI])="A",MAX([A]),MAX([KPI])="B",MAX([B]),MAX([KPI])="C",MAX([C]),MAX([KPI])="D",[MIN OF D])

9.png

 

Or create calculated columns

MIN OF D2 = CALCULATE(MIN([D]),ALLEXCEPT(Sheet4,Sheet4[cgranty_ode]))

Custom Score QED2 = SWITCH(TRUE(),[KPI]="A",[A],[KPI]="B",[B],[KPI]="C",[C],[KPI]="D",[MIN OF D])

Best Reagrds

Maggie

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @Anonymous

Since it is more difficult to do in power query, you could try to create measures in power pivot.

When you open the power bi desktop, instead going to "Edit Queries", just from "Home", you can select "New Measures" or "New Column".

Measures as below

MIN OF D = CALCULATE(MIN([D]),ALLEXCEPT(Sheet4,Sheet4[cgranty_ode]))

Custom Score QED = SWITCH(TRUE(),MAX([KPI])="A",MAX([A]),MAX([KPI])="B",MAX([B]),MAX([KPI])="C",MAX([C]),MAX([KPI])="D",[MIN OF D])

9.png

 

Or create calculated columns

MIN OF D2 = CALCULATE(MIN([D]),ALLEXCEPT(Sheet4,Sheet4[cgranty_ode]))

Custom Score QED2 = SWITCH(TRUE(),[KPI]="A",[A],[KPI]="B",[B],[KPI]="C",[C],[KPI]="D",[MIN OF D])

Best Reagrds

Maggie

Anonymous
Not applicable

@v-juanli-msftThanks Maggi! I created calculated column and this solution worked for me except for the fact that I had to add few more columns under ALLEXCEPT to get my data blended to my view. I am now able to restrict the values and have a single value in each row. Also I had to filter out the blanks from the resulted colum in order to not have a empty cell creating the same issue. 🙂 Man Very HappyRobot wink

PBI Community solved.png

 

 

 

 

 

edhans
Super User
Super User

In Power Query, have you tried using the GROUP BY transformation? For your KPI column, select the MIN operation.

 

If that doesn't work, post a file with some sample data so we can understand it better.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

@edhans Thank you for the suggestion! I had tired grouping the KPI column, but it doesn't work as per my requirement. But the solution provided by @v-juanli-msft works for me. Thank you for the support. 🙂 Man Surprised

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.