Matrix with a measure for value?

[YupeiChen]

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?

[PattemManohar]

@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"

[HotChilli]

Might be easier in power query.  I've had a play around with your data.  If you can follow this advanced editor code

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)

[Greg_Deckler · Accepted solution]

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.

[YupeiChen]

---

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

@PattemManohar and @HotChilli

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.