Provide overall status not successful (Calculate > Countrows > Filter)

[RickSanchez2043]

Hi! I've tried with the help of OpenAI to get to a working formula/column, but had no luck.

My source data is pulled from Smartsheets and is about the status of movies and series media. We receive media over time, but I want to repport if the media we expect to launch in the coming week is ready. For movies its simple, as the source data only has 1 line which is either 'pending' or 'ready'. For series it's more complex, as we can receive 6 episodes in total, but just 2 for this week. I would like to report for the current week if the series is 'Complete' when those 2 episodes are ready (ignoring any episodes in the future).

The data i'm using is following:

Series	Week	Final state
Name of movie or series	To help filter on content launching this week (eg 'This week')	The state of the item itself (Ready or Pending)
Movie title A	This week	Ready
Series title A	This week	Ready
Series title A	This week	Ready
Series title A	Not this week (eg 'Next week' or 'Other')	Pending

My goal is to add a 4th column which should show the 'series status' for items that have 'week = This week'. A bit like the below, but instead of having 'WIP' on eps 5 + 7 (which are 'This week' + both 'Ready'), it should show 'Complete':

In the end it should show the series as 1 row only, but to show the source data is correct, i kept it in on the above. In the end i would like to see 'Complete' or 'WIP' like so, with a 'count' on episodes:

The formula i got (and isn't working as i would like to) is following:

Series Status =

VAR SeriesName = 'TEST Max Delivery source'[Series]

VAR ThisWeek = MAX('TEST Max Delivery source'[Week]) = "This week"

VAR ReadyEpisodesThisWeek =

    CALCULATE(

        COUNTROWS('TEST Max Delivery source'),

        FILTER(

            ALL('TEST Max Delivery source'),

            'TEST Max Delivery source'[Series] = SeriesName &&

            ThisWeek &&

            'TEST Max Delivery source'[Final state] = "Ready"

        )

    )

VAR PendingEpisodesThisWeek =

    CALCULATE(

        COUNTROWS('TEST Max Delivery source'),

        FILTER(

            ALL('TEST Max Delivery source'),

            'TEST Max Delivery source'[Series] = SeriesName &&

            ThisWeek &&

            'TEST Max Delivery source'[Final state] = "Pending"

        )

    )

RETURN

    IF(

        PendingEpisodesThisWeek = 0,

        "Complete",

        IF(ReadyEpisodesThisWeek > 0, "WIP", "Not Ready")

    )

Curious if anyone has a trick to get there!

[RickSanchez2043]

Ow cool! Thanks for the speedy response btw!

It's not working fully yet, so I must be missing something. I added the index, which also shows in the table now:

Also the 3 new messurements show no errors, as the table looks like this:

I left in the 'Flag' as without filtering it shows '1' on both a pending items for next week and a ready item for this week. If i would filter, it would miss that 3rd item. It's also not showing 'Complete' yet for the 2 items that fall in this week and are ready.

In the final table i would also want to reduce it to only showing the series name + series status for that week. So 'Winning Time' - This week - Complete.

I also updated the reference on some columns in the 3 new meeasures to match the right name of the data:

Series Status = 
var _select=
SELECTCOLUMNS(FILTER(ALL('TEST Max Delivery source'),'TEST Max Delivery source'[Series]=MAX('TEST Max Delivery source'[Series])
&&'TEST Max Delivery source'[Week]="This week"),"status",'TEST Max Delivery source'[Final state])
return
IF(
    NOT("Pending") in _select && "Ready" in _select,"WIP",
IF(
       NOT( "Pending")in _select,"Complete",
"Not Ready"
))

Count_group = 
COUNTX(
    FILTER(ALLSELECTED('TEST Max Delivery source'),
  'TEST Max Delivery source'[Series]=MAX('TEST Max Delivery source'[Series])&&[Series Status]=[Series Status]),[Final state])

Flag = 
var _minindex=
MINX(
    FILTER(ALL('TEST Max Delivery source'),
    'TEST Max Delivery source'[Series]=MAX('TEST Max Delivery source'[Series])&&'TEST Max Delivery source'[Week]=MAX('TEST Max Delivery source'[Week])&&'TEST Max Delivery source'[Final state]=MAX('TEST Max Delivery source'[Final state])),[Index])
return
IF(
    MAX('TEST Max Delivery source'[Index])=_minindex,1,0)

PS. it would also be ok if the only 2 returned statuses would be 'complete' or 'WIP' (no pending = complete, else WIP).

It feels we're close!

[v-yangliu-msft]

Hi  @RickSanchez2043 ,

Here are the steps you can follow：
1. In Power Query -- Add Column – Index Column – From 1.

2. Create measure.

Series Status =
var _select=
SELECTCOLUMNS(FILTER(ALL('TEST Max Delivery source'),'TEST Max Delivery source'[Series]=MAX('TEST Max Delivery source'[Series])
&&'TEST Max Delivery source'[Week]="This Week"),"status",'TEST Max Delivery source'[Final status])
return
IF(
    NOT("Pending") in _select && "Ready" in _select,"WIP",
IF(
       NOT( "Pending")in _select,"Complete",
"Not Ready"
))

Flag =
var _minindex=
MINX(
    FILTER(ALL('TEST Max Delivery source'),
    'TEST Max Delivery source'[Series]=MAX('TEST Max Delivery source'[Series])&&'TEST Max Delivery source'[Week]=MAX('TEST Max Delivery source'[Week])&&'TEST Max Delivery source'[Final status]=MAX('TEST Max Delivery source'[Final status])),[Index])
return
IF(
    MAX('TEST Max Delivery source'[Index])=_minindex,1,0)

Count_group =
COUNTX(
    FILTER(ALLSELECTED('TEST Max Delivery source'),
  'TEST Max Delivery source'[Series]=MAX('TEST Max Delivery source'[Series])&&[Series Status]=[Series Status]),[Final status])

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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