Use column value in new column formula

[Streeph]

I have a source table (IVS_DATA) with a lot of columns, each holding boolean values (TRUE, FALSE or null). Column headers are 3-digit reason codes (e.g. '209').

Now, I want to create a new table in which the total 'TRUE' count for all reasons is calculated. My screenshot shows a first attempt: a static column 'Reason' (representing the reason codes) and a a calculated column for the total per reason code.

In this example the total of 'TRUE' values for the '209' reason is calculated. Is there a way to replace the statical "209" in the column formula with the value in the preceeding column? Or is there another way I should create this column or table?   

[Streeph]

@Greg_Deckler That would make my source table explode. My source table easily contains many hundredthousands records, and there are about 20 reason colums for each record ... 

[Anonymous]

Hi @Streeph ,

According to your statement, I think the column amount like 209 in IVS_DATA table should be the same as the row amount of the data in Reason column.

As mentioned as above, the calculation in Power BI is based on columns. So it is better you to transform your data by UNPIVOT function to make IVS_DATA contain [REASON] column and [Status] column.

If there are too many data in your table and this function will explode your table into too many rows, you can do some optimazation on your table.

For reference: Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards,
Rico Zhou

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

[Streeph]

Hi @Anonymous, I tried unpivoting, but (apart from a huge multiplication of rows) that also lead to duplicating the unique identifiers in my source table...

Before unpivot

	Reason_1	Reason_2	Reason_3
ID1	TRUE	FALSE	FALSE
ID2	FALSE	TRUE	TRUE
ID3	FALSE	TRUE	FALSE

After unpivot:

	Reason	Value
ID1	Reason_1	TRUE
ID1	Reason_1	FALSE
ID1	Reason_1	FALSE
ID2	Reason_2	FALSE
ID2	Reason_2	TRUE
ID2	Reason_2	TRUE
ID3	REason_3	FALSE
ID3	REason_3	TRUE
ID3	REason_3	FALSE

[FREDPI]

I think you will need to transpose the (IVS_DATA) Tabel in Power Query.
So you can get a a table with 2 columns.

1. Reason Code 
2. Status (true,false,blank)

Then you can create a measure that calculates the number of times TRUE is happening for a specifiek reason code.

[Greg_Deckler]

@Streeph Typically you would unpivot the TRUE/FALSE columns, put Reason in a table and create a measure that calculates the count.