Forum Discussion
Advocate Icombine column into one column with if-statement
Dear Power BI users,
I have a problem which I can not find the answer to. I want to use the input of a table to create a new column in this table. My data looks something like this:
UPN License1 License 2 License 3 License 4 License 5
SomeUPN null 1 1 null null
If a column of a license is filled with an "1", it means it has this kind of license. There are related costs to this type of license for example $5 to license 2 and $9 to license 3.
I want my data to look something like this:
UPN License1 License 2 License 3 License 4 License 5 TotalCosts
SomeUPN null 1 1 null null 14
I cannot seem to find the answer to this. Can you please help me?
With kind regards,
9 Replies
- vanessafvg
Community Champion
have you tried using the switch() funcatoin or nested if statements could work too? (although there might be better options but those are what come to mind),
- dkay84_PowerBI
Microsoft Employee
I solved this entirely within the query editor. Assuming you have a License Costs table in this form:
License # | Cost
1 | 15
2 | 20
etc.
you can do some ETL processes and then a merge to get costs.
On the table with the UPN column, select the UPN column and unpivot other columns. Based on your description, on the new attribute column, replace "License " with nothing to get just the license # (which you will need to change to math the data type of the License # column in your cost table). Now, depending on how you want to display the data, you have two options. You can either remove the "null" rows or convert them to "0". Then, you will do a merge using the license number from the UPN table and the Cost table. This will get your license cost, and you can either remove the null rows or add a custom column that multiplies the Cost by the Value (0 or 1).
- dkay84_PowerBI
Let me add to my previous post:
You have the data as described and you want to add a cost column for each UPN, based on the sum of the costs of each of the licenses that are either present (1) or not present (0 or null).
Assuming the License Costs look up table, as I mentioned before, here are the steps you need to follow in the query editor:
1. Select the UPN column and "unpivot other columns"
2. Make sure the license # column matches the data type of the license # column in the License Cost table
3. Do a Merge to get the cost of each type of license
4. Add a custom column that multiplies the indicator column (1 or 0/null) by the Cost column (from the merge)
5. Group By using UPN for the group by column and the new column will sum the Cost column
6. Close and Apply, and then create a relationship (UPN is the key) between this table and the original data table
7. Insert a table visual, put UPN and each of the license columns from the original table, and the Cost column from the created table
You can see with my dummy data that it acheives what you are looking for
- parry2k
Super User
As suggested by dkay84_PowerBI, is the way to go, assuming you have rates tables, if not then you can always write dax as below and hard code prices (not elegant solution)
Cost = if(UPN[License1] = blank(), 0, UPN[License1]*2)+if(UPN[License2] = blank(), 0, UPN[License2]*5)+if(UPN[License3] = blank(), 0, UPN[License3]*9)+if(UPN[License4] = blank(), 0, UPN[License4]*15)+if(UPN[License5] = blank(), 0, UPN[License5]*20)
- parry2k
Do you have rate stored in a different table? I believe rate is based on UPN. If that is the case then you need to create relation between your tables and then use simple formula to calculate cost, let me know if you need further assitance.
- dkay84_PowerBI
I believe Cost is based on the sum of the individual license costs. Each UPN could have different licenses, denoted by a null or numeric value under each license's column.
