Forum Discussion

Sam4u's avatar
Sam4u
Frequent Visitor
9 years ago
Solved

Need help with creating a measure

So I have 2 table with many to many relationship (example below) and I am trying to multiply one field of table 1 to another field in table 2. As easy as it sounds, for some reason I cannot figure it...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    Hmm, I'm close but not quite there. Given:

     

    Table WorkOrders
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAUMlHaWIyChzC0sgw9BAKVYHJmMEFHB0cjY0MgYyLJEkjJG1GCFrMUHWYowkYQrkB7j7mZoZQrXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkOrder = _t, MaterialType = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkOrder", Int64.Type}, {"MaterialType", type text}, {"Amount", Int64.Type}})
in
    #"Changed Type"


Table MaterialTypes
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRMjE0NQACQyDTUClWJ1opIjLK3MISIWMOZOoZY5WyAEmZg6UC3P1MzQzhUmCj9cywSpmApEyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MaterialType = _t, Account = _t, #"%Distribution" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MaterialType", type text}, {"Account", Int64.Type}, {"%Distribution", type number}})
in
    #"Changed Type"



Table Materials
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVaKiIwyt7AEMwPc/UzNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Materials = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Materials", type text}})
in
    #"Changed Type"

    With many-to-many via "Materials" columns, created measure:

     

    MyAmount = SUM([Amount])*CALCULATE(SUM(MaterialTypes[%Distribution]),RELATEDTABLE(MaterialTypes))

    Created Matrix visualization:

    Rows: WorkOrder from WorkOrders table

    Columns: Accounts from MaterialTypes table

    Values: MyAmount measure

     

    Close but returns a value when there is no relationship.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Hmm, I'm close but not quite there. Given:

 

Table WorkOrders
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQAAUMlHaWIyChzC0sgw9BAKVYHJmMEFHB0cjY0MgYyLJEkjJG1GCFrMUHWYowkYQrkB7j7mZoZQrXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [WorkOrder = _t, MaterialType = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkOrder", Int64.Type}, {"MaterialType", type text}, {"Amount", Int64.Type}})
in
    #"Changed Type"


Table MaterialTypes
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRMjE0NQACQyDTUClWJ1opIjLK3MISIWMOZOoZY5WyAEmZg6UC3P1MzQzhUmCj9cywSpmApEyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MaterialType = _t, Account = _t, #"%Distribution" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MaterialType", type text}, {"Account", Int64.Type}, {"%Distribution", type number}})
in
    #"Changed Type"



Table Materials
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVorViVaKiIwyt7AEMwPc/UzNDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Materials = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Materials", type text}})
in
    #"Changed Type"

With many-to-many via "Materials" columns, created measure:

 

MyAmount = SUM([Amount])*CALCULATE(SUM(MaterialTypes[%Distribution]),RELATEDTABLE(MaterialTypes))

Created Matrix visualization:

Rows: WorkOrder from WorkOrders table

Columns: Accounts from MaterialTypes table

Values: MyAmount measure

 

Close but returns a value when there is no relationship.

Sam4u's avatar
Sam4u
Frequent Visitor
9 years ago

yep, it seems to just multiply the amount regardless it the given WO has the MAT or not. I.e when I do it, it gives me 

 

Where 10, 6 and 4 should not exist... seems like the measure is missing a filter for the aplicable WO... 

 

I can add count to it :

MyAmount2 = SUM(WorkOrders[Amount])*CALCULATE(SUM(MaterialTypes[%Distribution]),RELATEDTABLE(MaterialTypes))*CALCULATE(COUNT(WorkOrders[WorkOrder]),RELATEDTABLE(MaterialTypes))

but then the totals are totally out of wack :S :(

 

I wish I knew DAX a little better .. :S

 

Another Edit:

 

I think I got it to work:

 

MyAmount = CALCULATE(SUM(WorkOrders[Amount])*SUM(MaterialTypes[%Distribution])*IF(ISNUMBER(count(WorkOrders[WorkOrder])),1,0)/COUNTA(Materials[Materials]),RELATEDTABLE(MaterialTypes))

It is not very elegant but seems to get the job done :)

 

 

smoupre - Thank yuo so much for pointing me into the right direction :)

 

 

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Nice one!