Forum Discussion
Custom Column, weird behaviour
Hi,
while adding some custom columns to a table (DAX) I encountered something weird, and I don't understand why it's happening:
I have a table:
| ID | Value |
| 1 | False |
| 1 | True |
| 1 | False |
| 1 | False |
| 2 | False |
| 2 | True |
| 3 | False |
| 3 | False |
| 3 | False |
What I want to achieve is: A custom column with a value of TRUE or FALSE, True when the value for an id is at least one time TRUE and FALSE when the value for an id is never TRUE. So in this case the custom column is True for both ID's 1 and 2 and False for ID 3.
I can achieve this by the the following statement:
isTrueID =
var arr = CALCULATETABLE(VALUES('Test'[ID]); 'Test'[Value] = TRUE())
RETURN
if('Test'[ID] in arr; TRUE(); FALSE())
Which results to this table (which is correct) :
| ID | Value | isTrueID |
| 1 | False | True |
| 1 | True | True |
| 1 | False | True |
| 1 | False | True |
| 2 | False | True |
| 2 | True | True |
| 3 | False | False |
| 3 | False | False |
| 3 | False | False |
Now when I add an extra custom column, for example: (doesn't matter wich DAX statement I use here):
ConcatColumn = CONCATENATE(Test[ID]; Test[Value])
My previous 'isTrueID' gets messed up and results in this table:
| ID | Value | isTrueID | ConcatColumn |
| 1 | False | False | 1FALSE |
| 1 | True | True | 1TRUE |
| 1 | False | False | 1FALSE |
| 1 | False | False | 1FALSE |
| 2 | False | False | 2FALSE |
| 2 | True | True | 2TRUE |
| 3 | False | False | 3FALSE |
| 3 | False | False | 3FALSE |
| 3 | False | False | 3FALSE |
Anyone an idea why this is happening? And how I can fix this?
Thank you!
Hi Anonymous
Not entirely sure why it's happening but try this expression for your first calculated column:
isTrueID = VAR SelID = Test[ID] VAR TrueRows = COUNTROWS( FILTER( ALL( Test ), Test[ID] = SelID && Test[Value] = TRUE() ) ) VAR Result = IF( TrueRows = 0, FALSE(), TRUE() ) RETURN ResultBest regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
4 Replies
Hi Anonymous
Not entirely sure why it's happening but try this expression for your first calculated column:
isTrueID = VAR SelID = Test[ID] VAR TrueRows = COUNTROWS( FILTER( ALL( Test ), Test[ID] = SelID && Test[Value] = TRUE() ) ) VAR Result = IF( TrueRows = 0, FALSE(), TRUE() ) RETURN ResultBest regards,
Martyn
If I answered your question, please help others by accepting it as a solution.Hi Anonymous
Had a bit of time to think about it...
The confusion comes from the fact that you have a row context and a filter context active at the same time.
Since you weren't explicitly overriding the filter context on the new 'ConcatColumn', the filter from the row context was still being applied.
For example, on row 1 of your table, the value in the ConcatColumn is "1FALSE".
Essentially, your CALCULATETABLE was asking for a list of unique IDs where Table[Value] = TRUE and Table[ConcatColumn] = "1FALSE".
Obviously, this would return an empty table and your IF statement would return FALSE.
Taking your original query, you could modify it to remove all filters as follows:
isTrueID = VAR arr = CALCULATETABLE( VALUES( 'Test'[ID] ), FILTER( ALL( Test ), 'Test'[Value] = TRUE() ) ) RETURN IF( 'Test'[ID] in arr, TRUE(), FALSE() )Hope this explains things.
Best regards,
Martyn
If I answered your question, please help others by accepting it as a solution.
& when adding the column 'ConcatColumn' to the filter in CALCULATETABLE, i get the back to the result I want to achieve.
Why is this working? And should I add every calculated column I add to the table in the filter for my calculated table?isTrueID = var arr = CALCULATETABLE(VALUES('Test'[ID]); 'Test'[Value] = TRUE(); Test[ConcatColumn] <> "") RETURN if('Test'[ID] in arr; TRUE(); FALSE())ID Value IsTrueID ConcatColumn 1 False True 1FALSE 1 True True 1TRUE 1 False True 1FALSE 1 False True 1FALSE 2 False True 2FALSE 2 True True 2TRUE 3 False False 3FALSE 3 False False 3FALSE 3 False False 3FALSE