Custom Column, weird behaviour

[Anonymous]

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!

[MartynRamsden · Accepted solution]

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
    Result

Best regards,
Martyn

If I answered your question, please help others by accepting it as a solution.

[MartynRamsden]

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.

[Anonymous]

Thank you, your solutions works! Still weird why my solutions breaks on other custom columns ...

[Anonymous]

& 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