Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @moosepng
Please try the below.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @moosepng ,
you can try this
DISTINCTCOUNT (TblName[itemnumber] );TblName[Status]="Open")
Thank & Regards
Rohit Jaiswal
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
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Hi, @moosepng
Please try the below.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Thanks again. I'm getting better I swear
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |