Forum Discussion

mafaber's avatar
mafaber
Icon for Helper II rankHelper II
5 years ago
Solved

Search within specific month

Hi,

 

I have a table with 4 columns, 'kpi', 'month', 'status' and 'value'. Status is either 'final' or 'preliminary'. I'd like to create a month_status column that returns 'final' if all the rows for the specific month are 'final', or 'prelim', if there is at least 1 row that has 'prelim' in column 'status'.

I tried modifying somehow below formula, but I could not make it do the search within a specific month only.

month_status =
if(
iserror(
search("prelim",
Table[status])
)
),
"final",
"prelim"
)
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    This could/should be done as a measure probably, but here is a column expression that should work.  Not sure if you are checking all KPIs within the month or within a given KPI for that month, but both variations shown.

     

    month_status =
    VAR vPrelimRows =
        CALCULATE (
            COUNTROWS ( table ),
            ALLEXCEPT (
                table,
                table[Month]
            ),
            table[Status] = "Preliminary"
        )
    RETURN
        IF (
            ISBLANK ( vPrelimRows ),
            "Final",
            "Preliminary"
        )

     

    replace Table with your actual table name.  If you are looking within a KPI and month use

    ALLEXCEPT(Table, Table[Month], Table[KPI])

     

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    This could/should be done as a measure probably, but here is a column expression that should work.  Not sure if you are checking all KPIs within the month or within a given KPI for that month, but both variations shown.

     

    month_status =
    VAR vPrelimRows =
        CALCULATE (
            COUNTROWS ( table ),
            ALLEXCEPT (
                table,
                table[Month]
            ),
            table[Status] = "Preliminary"
        )
    RETURN
        IF (
            ISBLANK ( vPrelimRows ),
            "Final",
            "Preliminary"
        )

     

    replace Table with your actual table name.  If you are looking within a KPI and month use

    ALLEXCEPT(Table, Table[Month], Table[KPI])

     

    Pat