Unpivoting: Create a new table with a new row if a value falls into two categories

[ExcelMonke]

Hi all,
Been racking my brain on the following problem in the following table:

I would effectively like to unpivot this table into the following intended result:

Notice how for IDs 104, 105, 110, they each fall within category A AND B, and therefore they have two lines. Is there a DAX formula for this? I unfortuntately cannot go into the transform data option due to some challenges with the data connection and would preferably do this only with DAX (which I understand may complicate things)

Thank you!

[VahidDM · Accepted solution]

Hi @ExcelMonke 

Try this DAX expression to find the output (Not in Measure or Cal column, use this expression as new table or add aggregation to it):

UNION(
    SELECTCOLUMNS(FILTER('Table', 'Table'[Category A] = 1), "ID", 'Table'[ID], "Category", "A"),
    SELECTCOLUMNS(FILTER('Table', 'Table'[Category B] = 1), "ID", 'Table'[ID], "Category", "B"),
    SELECTCOLUMNS(FILTER('Table', 'Table'[Category C] = 1), "ID", 'Table'[ID], "Category", "C"),
    SELECTCOLUMNS(FILTER('Table', 'Table'[Category D] = 1), "ID", 'Table'[ID], "Category", "D")
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[ExcelMonke]

Thanks so much for the quick response! I am a bit new to Power BI... Usually when creating a new DAX I just create a new measure. How do I do this without create a new measure/column?

[VahidDM]

Can you let me know how do you want to use the output in your report?

BTW, you can create a new table by clicking on New Table icon on Modeling tab, and use the previous DAX Expression:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[ExcelMonke]

Thanks! Effectively, I would like to have my report to show:
% of category A, B ... D.

The reason why I want (need) to unpivot is because I want to use the category names in the small multiples field within the 100% stacked bar chart (there are some additional values I am adding in the 100% stacked bar chart, but these are the key ones).

I am running into an issue with the above DAX though, after my closed parentheses with the first FILTER statement, I am getting an "Unexpected expression" error. Am I doing something wrong?

[VahidDM]

Make sure to change the 'Table' name in the formula to the right name from you PBI report

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[ExcelMonke]

This has been very helpful, thank you!