Forum Discussion

ruesaint_denis's avatar
ruesaint_denis
Icon for Helper I rankHelper I
4 years ago
Solved

IF statement measure

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!! 

 

DAX Calculation
If statements
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

2 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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).

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.