Forum Discussion

HuwThomas's avatar
HuwThomas
Frequent Visitor
8 years ago
Solved

Returning IDs based on a specified MIN value

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 ...
Need Help
  • danextian's avatar
    danextian
    8 years ago

    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's avatar
HuwThomas
Frequent Visitor
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    HuwThomas
    Frequent Visitor
    8 years ago

    Anonymous,

     

    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's avatar
      danextian
      Icon for Super User rankSuper User
      8 years ago

      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's avatar
        HuwThomas
        Frequent Visitor
        8 years ago

        Hi danextian,

         

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