cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
YupeiChen Frequent Visitor
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;

 

SampleIDWeigh StateFusion State
1FAILED (12)FAILED (100)
2FAILED (15)FAILED (104)
3FAILED (307)FAILED (12)
4SUCCESSFAILED (12)
5SUCCESSFAILED (16)
6FAILED (15)FAILED (17)
7FAILED (15)FAILED (40)
8SUCCESSFAILED (42)
9SUCCESSSUCCESS
10FAILED (40)FAILED (37)
11FAILED (307)FAILED (38)
12FAILED (307)FAILED (38)
13FAILED (307)FAILED (38)
14FAILED (307)FAILED (99)
15FAILED (307)FAILED (99)

 


I want it displayed like this: 

 WeighFusion
FAILED (12)12
FAILED (15)30
FAILED (16)01
FAILED (17)01
FAILED (37)01
FAILED (38)03
FAILED (40)11
FAILED (42)01
FAILED (99)02
FAILED (100)01
FAILED (104)01
FAILED (307)60

 

How do I go about writing a measure for this?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
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


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

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User IV
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


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

Proud to be a Datanaut!

View solution in original post

Super User I
Super User I

Re: Matrix with a measure for value?

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)

Super User I
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"

image.png



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

Proud to be a Datanaut !





YupeiChen Frequent Visitor
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...

 

 

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors