Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I have a column like "Total = [Column 1] + [Column 2] + [Column 3]".
Now I want to check whether a [Column 3] extsts or not and if yes then add that to total else not.
For ex., "Total = [Column] + [Column 2] + (IF [Column 3], [Column 3], 0).
I am getting error if I use above formula and that [Column 3] does not exists.
I am new to Power BI and hence don't know if certain check like that can be made or not. Please help.
Solved! Go to Solution.
@Anonymous
If the column names are fixed and known then you do something like below in PQ.
Add Column like
= try [Custom.1]+[Custom.2]+[Custom.5] otherwise [Custom.1]+[Custom.2]
In my example, I do not have Column.5 so it's not considered
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
I have a similar kind of requirement. My query returns a number of fields of which I am using three fields to generate a report. Fields are ID, Description and Quantity. The requirement is such that to view in the form of pivot table (Matrix equivalent in Power BI) with Id in Rows, Description in Columns and Quantity in values. The query generates different values for Description based on time of execution and out of the values 4 of them belong to a category (Reason for cancellation). So for example there can be only two values in description related to cancellation or maybe the query can generate all four values related to cancellation. Now I need to create a new column Total adding all the cancelled reasons together. How can I achieve this when the columns are dynamic?
Based on the above suggestions I was trying a nested try otherwise something like this below. I am not sure if this is a corrct way to frame the custom column calculation. It returns all null values.
try [col 1] + [col2] + [col3] + [col4] otherwise try [col1] + [col2] + [col3] otherwise try all other 3 column combinations otherwise try all 2 column combinations otherwise try all single column.
Is there any better way to solve the problem?
@Anonymous
What do you mean by "Column Exist?" . Do you need to check if a value exists in Column 3 then?
This is the model not in Power Query
Total = [Column 1] + [Column 2] + (IF( [Column 3]=Blank(),0, [Column 3]))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Actually, In Power Query, I am getting certain columns which are to be added in Model. But going on to generalize that query on certain other similar projects, I get some additional columns in Power Query. Hence, the Total column in Model can't be evaluated like that. It should check for the existence of certain ones and then add, otherwise it throws error.
Though it seems simple but I am not able to generalize it on multiple projects.
@Anonymous
If the column names are fixed and known then you do something like below in PQ.
Add Column like
= try [Custom.1]+[Custom.2]+[Custom.5] otherwise [Custom.1]+[Custom.2]
In my example, I do not have Column.5 so it's not considered
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Anonymous
You cann't do this way, column3 will be there forever, you can check for value greater than 0 or not like this...
Total = [Column] + [Column 2] + (IF [Column 3]>0, [Column 3], 0)
Thanks & Regards,
Mohammed Adnan
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
User | Count |
---|---|
94 | |
90 | |
83 | |
76 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |