Forum Discussion
How to create custom column based on value in cell?
Hi guys,
My data has the following structure:
| ID | Question | Answer |
| 1 | Contractor? | Firm A |
| 1 | Completed work? | Yes |
| 1 | Paid? | No |
| 2 | Contractor? | Firm B |
| 2 | Completed work? | No |
| 2 | Paid? | No |
Basically i have data on answers from a questionaire, and questions are grouped by ID. However, i want to add a 4th column, per ID that displays the name of the contractor, to nicely visualize and work with the data.
Like this:
| ID | Question | Answer | Questions from contractor: |
| 1 | Contractor? | Firm A | Firm A |
| 1 | Completed work? | Yes | Firm A |
| 1 | Paid? | No | Firm A |
| 2 | Contractor? | Firm B | Firm B |
| 2 | Completed work? | No | Firm B |
| 2 | Paid? | No | Firm B |
Any help would be greatly appreciated!
pls try this
Column = VAR t1 = [ID] RETURN MAXX( FILTER(ALL('Table'),'Table'[ID]=t1&&'Table'[Question]="Contractor?"),[Answer])
3 Replies
pls try this
Column = VAR t1 = [ID] RETURN MAXX( FILTER(ALL('Table'),'Table'[ID]=t1&&'Table'[Question]="Contractor?"),[Answer])This totally helped! Thank you so much. Could you explain the code so that i can learn what is does?
Your DAX code is used to create a calculated column in Power BI or Power Pivot, based on specified conditions and operations. Let's break it down step by step:
1) VAR t1 = [ID]: In this line, a temporary variable t1 is created, which holds the value of the current row's [ID] column. This is done to avoid repeated access to [ID] in subsequent operations.
2) FILTER(ALL('Table'), 'Table'[ID] = t1 && 'Table'[Question] = "Contractor?"): Here, the FILTER function is used to filter the 'Table' based on certain conditions. ALL('Table') is used to ignore any existing filters on the table and work with it entirely. The filtering conditions involve comparing the [ID] column with the temporary variable t1 and the [Question] column with the text "Contractor?".
3) MAXX(..., [Answer]): The MAXX function is applied to the filtered result and calculates the maximum value of the [Answer] column within the filtered table.
As a result, for each row in the [ID] column, the maximum value of the [Answer] column satisfying the filtering conditions based on [ID] and [Question] will be computed.
The code appears logical and performs the described actions. However, it's worth considering performance implications, as using FILTER(ALL(...)) can potentially impact performance, especially with large datasets.
