Forum Discussion
Use column value in new column formula
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?
5 Replies
Streeph Typically you would unpivot the TRUE/FALSE columns, put Reason in a table and create a measure that calculates the count.
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 ...
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
