Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RickSanchez2043
New Member

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

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:

 

SeriesWeekFinal state
Name of movie or seriesTo help filter on content launching this week (eg 'This week')The state of the item itself (Ready or Pending)
Movie title AThis weekReady
Series title AThis weekReady
Series title AThis weekReady
Series title ANot 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':

 

RickSanchez2043_0-1695195966476.png

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:

RickSanchez2043_1-1695196497973.png

 

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!
2 REPLIES 2
RickSanchez2043
New Member

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:

 

RickSanchez2043_0-1695380159635.png

 

RickSanchez2043_1-1695380194108.png

 

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

 

RickSanchez2043_3-1695381488335.png

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
Community Support
Community Support

Hi  @RickSanchez2043 ,

 

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

vyangliumsft_0-1695374719539.png

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.

vyangliumsft_1-1695374719542.png

4. Result:

vyangliumsft_2-1695374801963.png

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.