Returning IDs based on a specified MIN value

[HuwThomas]

Good morning,

I'm relatively new to Power BI and am having trouble with returning IDs where the Staus is only 5, where IDs appear multiple times in the table. Below is a simplified version of my data, with either a Status of 2 or 5.

I wish to return the rows of IDs that only have a 5 status, so do not appear again in the list with a 2 status. I have been trying to do this where MIN = 5 but have been unable to get this to work.

Many thanks,

Huw

ID	Status
1	2
2	2
3	2
2	5
1	2
4	5
5	2
6	5
5	2

[v-yuezhe-msft]

@HuwThomas,

To get the table as request, we can use the DAX function CALCULATETABLE in Power BI desktop.

1. Enter the data accordingly, here we can get the original table named TEST.
2. Create a new table by clicking the New Table icon. After that we are going to use the formula as below to get the data what we need.

Status 5 = CALCULATETABLE(TEST,FILTER(TEST, TEST[STATUS] = 5))

Alternatively, we can filter the TEST table directly in Query Editor or using visual level filter .

 
For more information, please refer to the pbix as attached.

Regards,
Lydia

[HuwThomas]

@v-yuezhe-msftThank you for your reply but this isn't quite what I was trying to achieve. I was hoping to return IDs that only have a 5 status, so if they also appear in the list with a 2 status then I do not wish to pull them through. So, in my test data I would want ID 4 and 6 but not ID 2.

Apologies, I probably didn't explain this clearly!

Regards,

Huw

[v-yuezhe-msft]

@HuwThomas,

Based on my test, we can take these steps to get the new table as request.

1. First we can create a measure:
Measure := DISTINCTCOUNT(TEST[STATUS])

2. Create a table visual and filter the table, set the Measure value to 1 and status value to  5. 

For more information, please check the pbix as attached.

Regards,
Lydia

[HuwThomas]

@v-yuezhe-msft,

That's a great workaround but limits the presentation of this information (forced to have the extra columns and unable to use other visuals). 

I've managed my own workaround which is using a new table, summarising the ID and summing the status column. This has worked so far so I will just have to hope that it does not cause any problems down the line!

My solution: 

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

Many thanks for the help,

Huw

[danextian · Accepted solution]

Hi @HuwThomas,

You can try creating a calculated column which checks whether the average of Status for each ID is 5. The formula below returns either true or false

Status5 = 
CALCULATE( 
 AVERAGE ( 'Table'[Status] ),
 ALLEXCEPT ( 'Table','Table'[ID] )
 ) = 5

[HuwThomas]

Hi @danextian,

That's great! Exactly what I was trying to do, many thanks!