Using DAX in PowerBi to change data format from wide to long

Question

I'm currently working on a Power BI project and I'm facing a challenge with transforming and summarizing the data in wide/horizontal format, I have a dataset structured as follows:

critical_level	id_trans	agent	period	type_a	type_b	type_c
1	a1	mark	may	0	0	1
1	a2	mark	may	1	1	1
0.5	a3	mark	may	1	0	0
1	a4	sam	may	1	0	1
0	a5	sam	may	1	1	0
1	a6	sam	may	1	0	0

The cols type_a, type_b and type_c are calculated columns created from the raw data.

This is how they are calculated, I have the same three cols in my raw source data but instead of a number I have a categorical value with the mark "Present" or "Not Present" so I transform them into 0 and 1 (1 if present).

My goal is to explode/pivot the data into long format as follows

critical_level	id_trans	agent	period	type_col
1	a1	mark	may	type_c
1	a2	mark	may	type_a
1	a2	mark	may	type_b
0.5	a3	mark	may	type_a
1	a4	sam	may	type_a
1	a4	sam	may	type_c
1	a5	sam	may	type_a
1	a5	sam	may	type_b
1	a6	sam	may	type_a

Note that I have already filtered out critical value equal to zero.

I would prefer a DAX solution, but all proposed solutions are more than welcome.

Answer 1

Here is the DAX solution.

Table 2 = 
UNION(
    SELECTCOLUMNS(FILTER('Table','Table'[type_a] <> 0), "critical_level", 'Table'[critical_level], "id_trans", 'Table'[id_trans], "agent", 'Table'[agent], "period", 'Table'[period], "type_col", "type a"),
    SELECTCOLUMNS(FILTER('Table','Table'[type_b] <> 0), "critical_level", 'Table'[critical_level], "id_trans", 'Table'[id_trans], "agent", 'Table'[agent], "period", 'Table'[period], "type_col", "type b"),
    SELECTCOLUMNS(FILTER('Table','Table'[type_c] <> 0), "critical_level", 'Table'[critical_level], "id_trans", 'Table'[id_trans], "agent", 'Table'[agent], "period", 'Table'[period], "type_col", "type c")
)