Creating Boolean column based on other columns (exists in SQL)

[benkenten]

Hi all 

Very new to PBI, I have two columns (JournalID and Reversing), with data. I want to create a third column, InReversing, that will evaluate to True, because there is minimum 1 Reversing within the JournalID (sample data below).

In SQL this is easy using Group By and Exists. I don't know how to start this with DAX/PQ. Can anyone help?

JournalID	Reversing	InReversing?
ABC	True	True
ABC	False	True
DEF	False	False
DEF	False	False

[Kishore_KVN · Accepted solution]

Hello @benkenten ,

I can suggest one approach for this problem. Create two columns and make sure to change the datatype of True/False column into Text before creating these two columns.

DAX for Column 1 will be: 

Numbering = IF(Data[Reversing]="True",1,0)

DAX for Column 2 will be:

Result = 
Var A = CALCULATE(SUM(Data[Numbering]),ALLEXCEPT(Data,Data[JournalID]))
Return
If(A>0,"True","False")

Output looks like this:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[tamerj1]

Hi @benkenten 

please try

InReversing =
TRUE
IN CALCULATETABLE (
VALUES ( 'Table'[Reversing] ),
ALLEXCEPT ( 'Table', 'Table'[JournalID] )
)