Forum Discussion
Create a Measure/Solution aggreagteing Data and comparing 2 columns
Hello,
I'm working with a dataset which is basically structured like this:
The columns Customer_1 and Customer_2 refer to the same Customer.
What I would like to do is create a Matrix Visual which shows
Customer 1 as line, SUM(Sales_Customer_1) as Value and SUM(Transactions_Customer_2) as Value in one line
I don't know though how to establish a relationship between the columns Customer_1 and Customer_2.
Is there any way to solve this with an DAX_Measure. The only solution I've found is creating an aggregated DAX-Table (GroupBy) for customer_2 and Sum(Transactions_Customer_2) and establish an 1:n-relationship with the existing and the new table.
I was wondering though if it is possible to solve this only via an dax measure.
Hi AC123 -you don’t need to create a new table or model relationship if you just want to align Customer_1 with aggregated values from Customer_2.
create a measure with below filter condition as follow:
Total Transactions_MatchedToCustomer1 =
CALCULATE(
SUM(Data[Transactions_Customer_2]),
FILTER(
ALL(Data),
Data[Customer_2] = MAX(Data[Customer_1])
)
)This works please check.
6 Replies
Hi AC123 -you don’t need to create a new table or model relationship if you just want to align Customer_1 with aggregated values from Customer_2.
create a measure with below filter condition as follow:
Total Transactions_MatchedToCustomer1 =
CALCULATE(
SUM(Data[Transactions_Customer_2]),
FILTER(
ALL(Data),
Data[Customer_2] = MAX(Data[Customer_1])
)
)This works please check.
Hi AC123
Just checking back on your question about combining Customer_1 and Customer_2 metrics into a single matrix line using DAX.
As shared earlier by danextian rajendraongole1 the recommended approach involves creating an aggregated DAX table (e.g., using GROUPBY or SUMMARIZE) for Customer_2 and establishing a 1:n relationship since both columns refer to the same logical entity but reside separately in the model.Were you able to try out that method or explore.
Let us know if you need any help happy to clarify things further!
Looking forward to your update!
Hi AC123
Following up on your query about merging Customer_1 and Customer_2 metrics into a single matrix line via DAX.
As mentioned earlier by rajendraongole1 danextian the suggested solution is to create an aggregated DAX table for Customer_2 and set up a 1:n relationship, since both fields represent the same entity but are stored separately in the model.Have you had a chance to try this approach yet? Let us know if you’d like any further clarification.
Hi AC123
You will need unpivot and then pivot your data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYggyVIrViVZyArKM0MScgSxjNDEXIMsETcwVyDJFE4OwHaGmGiGJOUFNQBYD2WSGJgayyQJNDGSToQFEMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer_1 = _t, Sales_Customer_1 = _t, Customer_2 = _t, Transactions_Customer_2 = _t, #"Entry Type" = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Entry Type", "Index"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}), #"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Attribute]), "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Sales", Int64.Type}, {"Transactions", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"}) in #"Removed Columns"Please see the attached pbix.
Thank you for the feedback. Is there any possibility to achieve this with DAX without changing the structure of the data set by unpivoting and pivoting it?
