cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mafaber
Helper II
Helper II

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"
)
1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


It works, thank you!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors