count the sum when row names equal to the column names

[Anonymous]

Hello everyone

I am trying to aggreate the first table (product table) with the second table (records table) as below:

(Both of them are virtual tables crated based on other tables.)

Product table

 

Records table

(Expected Result: how many used records are there for product A/ B/ C/ D)

I have considered transpose the records table, but since the customer rows are not just 10 in real data, I think it does not work.

Is there anyway to write a DAX saying if row names equals to columns names then sum up the value in the column or if there is any better way to solve it?

Thank you for your help in advance.

[Anonymous]

@Anonymous  - You suggested transposing the data. Is there actually a column for every product? If so, you can use "Unpivot" in Power Query. You will want to then filter the rows to only include the rows where value = 1. Here is a Power Query script:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMDOB2rE61kBOUhMEjUGEmtAVytCZIIVG0sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", Int64.Type}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 1)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Product"}})
in
    #"Renamed Columns"

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[jdbuchanan71]

@Anonymous 

Unpivot is going to be the way to go.  In the query editor, if you select only the customer column you can choose 'Unpivot other columns' so if new products come in the step will work with the new columns.

If your original table with customer and product has a bunch more fields than just products used you migh consider making a new query that references the original, removes the extra columns then does the unpivot and you do your sums from there.

[Anonymous]

Hi @jdbuchanan71  those two tables are created virtually from other tables by lookupvalue or related function. Therefore, I found I cannot use unpivot. (Please correct me if I am wrong.)

Do you see there are any other ways?

[v-lid-msft]

Hi @Anonymous ,

We think the product name is constant since you create multi columns with the product name in a virtual table. If it is constant, then We can create a calculated column in the product table using following formula.

Used Records =
SWITCH (
    [Product Name],
    "A", SUM ( 'Records table'[A] ),
    "B", SUM ( 'Records table'[B] ),
    "C", SUM ( 'Records table'[C] ),
    "D", SUM ( 'Records table'[D] )
)

Or following one if you just want to count no-zero number:

Used Records =
SWITCH (
    [Product Name],
    "A", COUNTROWS ( FILTER ( 'Records table', 'Records table'[A] <> 0 ) ),
    "B", COUNTROWS ( FILTER ( 'Records table', 'Records table'[B] <> 0 ) ),
    "C", COUNTROWS ( FILTER ( 'Records table', 'Records table'[C] <> 0 ) ),
    "D", COUNTROWS ( FILTER ( 'Records table', 'Records table'[D] <> 0 ) )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello everyone

I really appreciate ALL solutions you shared. I will test them shortly and marked one as the final solution for me. Thanks for your time!

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[jdbuchanan71]

@Anonymous 

The fact that we are talking about unpivoting a virtual table made with lookups and relationships makes me think we are going about this the wrong way.  Are you able to share your data model so we can see the structure?

[Anonymous]

@Anonymous  - If you really need to do it in DAX, you could refer to this idea. But if possible, it's best to push all of the logic, including the "virtual tables", back to Power Query.