cancel
Showing results for
Did you mean:
Frequent Visitor

Matrix with a measure for value?

Hi, I'm stumped on how to create a matrix a certain way.

I have a data table like this;

 SampleID Weigh State Fusion State 1 FAILED (12) FAILED (100) 2 FAILED (15) FAILED (104) 3 FAILED (307) FAILED (12) 4 SUCCESS FAILED (12) 5 SUCCESS FAILED (16) 6 FAILED (15) FAILED (17) 7 FAILED (15) FAILED (40) 8 SUCCESS FAILED (42) 9 SUCCESS SUCCESS 10 FAILED (40) FAILED (37) 11 FAILED (307) FAILED (38) 12 FAILED (307) FAILED (38) 13 FAILED (307) FAILED (38) 14 FAILED (307) FAILED (99) 15 FAILED (307) FAILED (99)

I want it displayed like this:

 Weigh Fusion FAILED (12) 1 2 FAILED (15) 3 0 FAILED (16) 0 1 FAILED (17) 0 1 FAILED (37) 0 1 FAILED (38) 0 3 FAILED (40) 1 1 FAILED (42) 0 1 FAILED (99) 0 2 FAILED (100) 0 1 FAILED (104) 0 1 FAILED (307) 6 0

How do I go about writing a measure for this?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

Re: Matrix with a measure for value?

Might need a measure, but what I think you really need is a separate table using this formula:

```StateTable =
DISTINCT(
UNION(
SELECTCOLUMNS('Table',"State1",[Weigh State])
SELECTCOLUMNS('Table',"State2",[Fusion State])
)
)```

Relate this table to your Weigh State and Fusion State columns in your table. And, since that will create an inactive relationship, you will need a measure:

`Fusion Count = CALCULATE(COUNT('Table'[SampleID]),USERELATIONSHIP(StateTable[State1],'Table'[Fusion State]))`

You should just be able to use the default Count aggregation for SampleID in your other one for Weigh State.

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

4 REPLIES 4
Super User IV

Re: Matrix with a measure for value?

Might need a measure, but what I think you really need is a separate table using this formula:

```StateTable =
DISTINCT(
UNION(
SELECTCOLUMNS('Table',"State1",[Weigh State])
SELECTCOLUMNS('Table',"State2",[Fusion State])
)
)```

Relate this table to your Weigh State and Fusion State columns in your table. And, since that will create an inactive relationship, you will need a measure:

`Fusion Count = CALCULATE(COUNT('Table'[SampleID]),USERELATIONSHIP(StateTable[State1],'Table'[Fusion State]))`

You should just be able to use the default Count aggregation for SampleID in your other one for Weigh State.

I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Super User I

Re: Matrix with a measure for value?

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJz9PRxdVHQMDTSROYZGGgqxepEKxkhC5qiKjGBKDFGEjQ2MNdEMxSkxAQoFhzq7OwaHIxF1hS7rBlE1gy3C8whKsxxqjCB+sICqw0mUPstUWRhLJCMoQGaYUg+hVpuaIjb+8YWUDVGRKjBE4xwNSa41VhaQtWYElATCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SampleID = _t, #"Weigh State" = _t, #"Fusion State" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SampleID", Int64.Type}, {"Weigh State", type text}, {"Fusion State", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Weigh State", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"SampleID"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value", "Attribute"}, {{"Count", each Table.RowCount(_), type number}}),
#"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Value", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows1", List.Distinct(#"Sorted Rows1"[Attribute]), "Attribute", "Count", List.Sum),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Value", "Weigh State", "Fusion State"})
in
#"Reordered Columns"```

I removed the SampleId column, unpivoted, grouped and then pivoted.  The data then appears as you want it (you need to clean up nulls and fix the column sorting after this)

Super User I

Re: Matrix with a measure for value?

@YupeiChen If you would like to achieve this in "Power Query", then please try this..

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJz9PRxdVHQMDTSROYZGGgqxepEKxkhC5qiKjGBKDFGEjQ2MNdEMxSkxAQoFhzq7OwaHIxF1hS7rBlE1gy3C8whKsxxqjCB+sICqw0mUPstUWRhLJCMoQGaYUg+hVpuaIjb+8YWUDVGRKjBE4xwNSa41VhaQtWYElATCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [SampleID = _t, #"Weigh State" = _t, #"Fusion State" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SampleID", Int64.Type}, {"Weigh State", type text}, {"Fusion State", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Weigh State", "Fusion State"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Only Selected Columns", {"Value", "Attribute"}, {{"Count", each Table.RowCount(_), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Value", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Attribute]), "Attribute", "Count"),
#"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Value", "Weigh State", "Fusion State"}),
#"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"Weigh State"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"Fusion State"}),
#"Sorted Rows1" = Table.Sort(#"Replaced Value1",{{"Value", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows1", each ([Value] <> "SUCCESS"))
in
#"Filtered Rows"```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Frequent Visitor

Re: Matrix with a measure for value?

@Greg_Deckler wrote:

Might need a measure, but what I think you really need is a separate table using this formula:

```StateTable =
DISTINCT(
UNION(
SELECTCOLUMNS('Table',"State1",[Weigh State])
SELECTCOLUMNS('Table',"State2",[Fusion State])
)
)```

Relate this table to your Weigh State and Fusion State columns in your table. And, since that will create an inactive relationship, you will need a measure:

`Fusion Count = CALCULATE(COUNT('Table'[SampleID]),USERELATIONSHIP(StateTable[State1],'Table'[Fusion State]))`

You should just be able to use the default Count aggregation for SampleID in your other one for Weigh State.

@Greg_Deckler
When I try to create a relationship it says I cannot create one between the two columns (Weigh State & State 1) because one of the columns must have unique values. I checked State 1 of Table and it appears to have a list of unique values though...

There is also a date column that I left out of my example, I need a slider to control the date range displayed so unfortunately doing it in PowerQuery won't work.

EDIT: I figured out my problem. I had null values in my state table.

Announcements

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!