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

[Anonymous]

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 😞 

 

[v-juanli-msft · Accepted solution]

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

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]

@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. 🙂 

[edhans]

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.

[Anonymous]

@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. 🙂