Forum Discussion
Create Multiple Calculated Columns in Transform or DAX at once
Hello,
I am trying to do a loop in Power BI since I have a dataset with multiple categories. Each category has it's own set of columns. In this example, there are categories A, B, and C, and each category has a numerator and denominator column. I would like to create a column in transform or DAX for each cateogry that calculates the ratio. In reality there are 20 categories and I have multiple calculated columns for each category I need to create. I am trying to reduce the manual steps needed. Below is an example of the table I'm trying to create without having to manually make 3 columns. Thanks!
Sounds more like a data format issue than anything else, you can get it into a form that is much easier to calculate on by following these steps in Power Query
Step 1: Unpivot
Step 2 Split the Attribute
Step 3: Repivot by Attribute.1
Step 4: add your division column
yeah it might have been faster to do it manuall lol, hope this helped
3 Replies
Sounds more like a data format issue than anything else, you can get it into a form that is much easier to calculate on by following these steps in Power Query
Step 1: Unpivot
Step 2 Split the Attribute
Step 3: Repivot by Attribute.1
Step 4: add your division column
yeah it might have been faster to do it manuall lol, hope this helped
Thanks! Makes sense that an unpivoted data structure would make measure creation much simpler. Do you know what the tradeoffs would be in performance? If I have 20 categories, in the current format I'd need 20 measures. If the data was unpivoted, the number of rows would increase by 20x but only 1 measure would be needed
