Create category based on column names of the table

[tmhalila]

Hello,

I need your help here I have a table with two columns that are supposed to be categories, and its total makes another column named total_participants

sample data

Expected output

I want to use this with the decomposition tree visual, so it can start with total the decomposition into category_1 and category_2. How can I do this only with either a calculated column or a measure without adding a calculated table due to the huge dataset I have? @Greg_Deckler  I saw your suggestion somewhere for using only SWITCH(TRUE() can you assist, please?

[danextian]

Hi @tmhalila 

You can unpivot Category_1 and Category_2 columns and that should create a column of categories. That should be the simplest solution. If for some reason, you don't want that route, you can create a disconnected table that holds the category names either in DAX or M (sample below) and create a measure to return the value for each category.

CategoryTable =
DATATABLE ( "Category", STRING, { { "Category 1" }, { "Category 2" } } )

Measure:

Value =
SWITCH (
    SELECTEDVALUE ( CategoryTable[Category] ),
    "Category 1", SUM ( 'FactTable'[Category 1] ),
    "Category 2", SUM ( 'FactTable'[Category 2] )
)

Use the category column from the disconnected table in the decomposition tree visual.

[tmhalila]

Thanks for your solution @danextian 

I am trying to avoid Unpivot and additional of the table for the two reasons; 

• The dataset has millions of records and keeps coming, to unpivot it may lead to additional rows
• The dataset has more sets of categories like the one shared so it will be confusing when trying to unpivot different sets.

So I think using a calculated column or measure can at least save performance.