IF statement measure

[ruesaint_denis]

Hi everyone, 

I can't seem to get this to work, I would like to get a measure what takes the Average PPS from my table, depending on certain conditions. 

These are the columns I'd like to work with. I'd like to write a meaure that takes this logic:

If ['Is Active']=True, take value of Average PPS, 

else if ['Company Status']=Open && Average PPS isnotblank, take the Average PPS when MAX['Updated At'], 

else "Please Check" 

I'm trying to create a matrix that has the Company ID as the rows, and this measure is one of the columns. 

Company ID does repeat, there shouldn't be more than 1 row for a company ID when ['Is Active']=True though.
I'm essentially trying to filter/manipulate which row to take for the Company ID's Average PPS. I don't really need to "calculate' anything, more than just filter. 

Thank you for the help!! 

[Anonymous · Accepted solution]

Hi @ruesaint_denis ,

I create a sample with data from your screenshot to have a test.

Measure:

Measure = 
VAR _TRUE_AVG = CALCULATE(SUM('Table'[Average PPS]),'Table'[Is Active] = TRUE())
VAR _Active_list = CALCULATETABLE(VALUES('Table'[Is Active]),ALLEXCEPT('Table','Table'[Company Id]))
VAR _Status_list = CALCULATETABLE(VALUES('Table'[Company Status]),ALLEXCEPT('Table','Table'[Company Id]))
VAR _MAXDATE = CALCULATE(MAX('Table'[Updated At]),FILTER(ALLEXCEPT('Table','Table'[Company Id]),'Table'[Company Status] = "Open"))
VAR _OPEN_AVG = CALCULATE(SUM('Table'[Average PPS]),FILTER( ALLEXCEPT('Table','Table'[Company Id]), 'Table'[Updated At] = _MAXDATE))
RETURN
IF(TRUE() in _Active_list,_TRUE_AVG, IF("Open" in _Status_list && _OPEN_AVG<>BLANK(),_OPEN_AVG,"Please Check"))

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SpartaBI]

@ruesaint_denis can you share a sample data in copy paste here and the desired result and logic (in words) for that result (the visual).