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

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!