New table with with DISTINCT IDs and TRUE condition

[moosepng]

How do I create a table with unique values [ID] where a condition ([Status] = A) is TRUE?

I have a Table where each row has a unique DateTime:

DateTime	ID	Status
25/10/2020 14:25	1001	A
29/10/2020 14:25	1001	B
1/12/2020 15:30	1002
5/02/2021 12:00	1001	B
15/04/2021 21:45	1002	B
20/04/2021 21:45	1003	A

I want to create a table with a List of DISTINCT IDs where Status = A
My expected result is:

ID

1001

1003

What I have so far is:

New_Table = 
FILTER(
    DISTINCT( Table[ID]),
    'Table'[Status] = "A"
)

The error I get is: "Column 'Housing' in table 'StatusChangeMerged' cannot be found or may not be used in this expression."

I get it, I don't include the [Status] in my new table, so it can't be found.

How do I inculde [Status} in my new table? Or how do I evaluate the FilterExpression on another table?

[Jihwan_Kim · Accepted solution]

Hi, @moosepng 

Please try the below for creating a new table.

New_Table =
SUMMARIZE ( FILTER ( 'Table', 'Table'[Status] = "A" ), 'Table'[ID] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[RJ251997]

Hi @moosepng ,

you can try this

DISTINCTCOUNT (TblName[itemnumber] );TblName[Status]="Open")

Thank & Regards 

Rohit Jaiswal

[Jihwan_Kim · Accepted solution]

Hi, @moosepng 

Please try the below for creating a new table.

New_Table =
SUMMARIZE ( FILTER ( 'Table', 'Table'[Status] = "A" ), 'Table'[ID] )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[moosepng]

How would you create a table with all values that have never had a status = A?
With result:

ID

1002

I though

New_Table =
SUMMARIZE ( FILTER ( 'Table', 'Table'[Status] <> "A" ), 'Table'[ID] )


But this returns

ID

1001

1002

because ID 1001 has also had Status = B

[Jihwan_Kim · Accepted solution]

Hi, @moosepng 

Please try the below.

New_Table never had A =

EXCEPT (

VALUES ( 'Table'[ID] ),

SUMMARIZE ( FILTER ( 'Table', 'Table'[Status] = "A" ), 'Table'[ID] )

)

[moosepng]

Replying to my own message just in case anyone stubles on this one.

NOT ( Table1[ID] IN VALUES ( Table2[ID] ) ) 

this worked to select IDs not selected in my New Table. got the solution from here: https://community.powerbi.com/t5/Desktop/Cross-filter-to-exclude/td-p/1448337

[moosepng]

Thanks again. I'm getting better I swear