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.
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 |
Solved! Go to 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
Proud to be a Super User!
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
@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
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
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
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
Proud to be a Super User!
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 |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |