Forum Discussion
Returning IDs based on a specified MIN value
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
AnonymousThank 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
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,
LydiaAnonymous,
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
