Subscription Snapshot Firing Prematurely

BrianJ · November 8, 2020, 6:42pm

Forum Members,

I’ve run into an interesting problem with the online service. In researching it, it seems this might be one of those frustrating things I have to live with until Microsoft makes fixing it a priority. However, I am always hopeful that some ingenious member(s) of this community has found a clever workaround.

Here’s my situation. I have the following report, which graphically tracks the status of management approvals of proposed allocations associated with various streams of funds coming into a revolving account. Funds may come in via receipts, transfers or (rarely) adjustments. Each stream has a separate approval chain, which also varies by which office “owns” the incoming funds. (None of this is particularly important here, except in understanding that the logic flows underlying the stoplights are very complex).

All of this runs like a champ in the service:

The problem comes when the user tries to subscribe to this report. At that point, the transmitted PDF shows the report, but with a bunch of broken graphical links, most importantly all the green stoplights missing. The reason for this apparently is that Power BI fires the snapshot before all the graphics are finished rendering. Because the green lights are deepest within the logic structure, they get rendered last, after the point at which the PDF snapshot is taken.

So, to try to improve this situation, I stripped out all the nonessential graphics, slicers, cards, etc., and also optimized the DAX to the best of my abilities (in real time, the report runs very quickly with no noticeable lag time in rendering the stoplights). However, even with these modifications the green stoplights are still breaking in the PDF:

It seems absurd to me that there is no way to delay the snapshot until after rendering is complete (or that’s not just built-in as the default). Has anybody else run into this problem, and if so how do you handle it?

Thanks in advance for your help.

Brian

PS - I know that if worse came to worst, I could do this in Power Query, which in retrospect perhaps I should’ve done from the start. However, there are some legitimate reasons I wanted to do this in DAX, plus I’m not super enthusiastic about rewriting hundreds of lines of extensively tested DAX code in custom M.

AntrikshSharma · November 8, 2020, 7:04pm

@BrianJ Can you show the DAX code that you have written?

MudassirAli · November 8, 2020, 7:15pm

@BrianJ Are you facing the same problem, when exporting the files to ppt? It happened to me also then I exported to ppt and then to pdf. One added step but it did work

BrianJ · November 8, 2020, 8:15pm

@MudassirAli,

Thanks – good thought. I hadn’t tried that, but unfortunately the PPT export breaks in the same way that the PDF does.

Brian

BrianJ · November 8, 2020, 8:22pm

@AntrikshSharma,

Sure – each of the six approvers has an individual DAX measure like the one below, but that is reflective of their specific roles in each of the respective approval chains. Thanks for taking a look.

Brian

OHC TMT Approval Status Icon =

VAR RedCircle = "🔴"   //approval needed
VAR GreenCircle ="🟢"  //approved
VAR BlackCircle ="⚫"  //not in approval chain
VAR BlueCircle ="🔵"   //Indicates debug problem – statement should not reach this level
VAR WhiteCircle ="⚪"  //approval chain not yet initiated
VAR RedX ="❌"         //rejected

VAR Icon =
SWITCH( TRUE(),
    SELECTEDVALUE( 'Master Transactions'[Transaction Type] ) = "Receipts",
        SWITCH( SELECTEDVALUE( 'Master Transactions'[Display Record Slicer Value] ),
            "Anticipated", WhiteCircle,
            "Received, Not Yet Circulated", WhiteCircle,
            "Approval Needed", 
                SWITCH( SELECTEDVALUE( 'Master Transactions'[cbas_ohcapproval] ),
                    864530000, RedCircle,
                    864530001, GreenCircle,
                    864530002, RedX,
                    "Not Yet Init1"
                ),
            "Not Yet Init2"
        ),
    CONTAINSSTRING( SELECTEDVALUE( 'Master Transactions'[Transaction Type] ), "Transfer" ),
        SWITCH( TRUE(),
            SELECTEDVALUE( 'Master Transactions'[Billing.cbas_comfcodeses.cbas_name] ) IN {"COMF11", "COMF12", "COMF22", "COMF24"},
                SWITCH( SELECTEDVALUE( 'Master Transactions'[cbas_ohcapproval3] ),
                    864530000, GreenCircle,
                    864530001, RedX,
                    864530002, RedCircle,
                    "Not Yet Init."
                ),
            BlackCircle
        ),
    CONTAINSSTRING( SELECTEDVALUE( 'Master Transactions'[Transaction Type] ), "Indirect" ),
        SWITCH( SELECTEDVALUE( 'Master Transactions'[cbas_approval3] ),
            864530000, GreenCircle,
            864530001, RedX,
            864530002, RedCircle,
            "Not Yet Init4"
        ),     
    CONTAINSSTRING( SELECTEDVALUE( 'Master Transactions'[Transaction Type] ), "Balance" ),
        SWITCH( SELECTEDVALUE( 'Master Transactions'[cbas_approval3] ),
            864530000, GreenCircle,
            864530001, RedX,
            864530002, RedCircle,
            "Not Yet Init5"
        ),  
    CONTAINSSTRING( SELECTEDVALUE( 'Master Transactions'[Transaction Type] ), "Adjustment" ),
       SWITCH( SELECTEDVALUE( 'Master Transactions'[cbas_approval3] ),
            864530000, GreenCircle,
            864530001, RedX,
            864530002, RedCircle,
            "Not Yet Init6"
        ),  
    BLANK()
)

RETURN
IF( ISINSCOPE( 'Master Transactions'[DARRF ID] ),
    Icon,
    BLANK()
)

AntrikshSharma · November 9, 2020, 7:01am

@BrianJ Try to build a full table which will contain all the Icons, and then iterate on it, that way FE won’t have to go back and forth for each cell and some data cache might be available to use. Idea is to retrieve everything at once and then let FE work on it, this technique has helped me in a lot of codes, worth a shot. Also what does DAX Studio have to say about this code?

BrianJ · November 9, 2020, 3:57pm

@AntrikshSharma,

Thanks very much for the recommendations. A couple of follow-up questions:

I certainly get the concept of putting the icons in a helper table, but given the structure of my measure, what’s the general construct you would use to iterate over the icons table? I’m just having a hard time picturing how I would set that up.
what would you suggest I look at in DAX Studio? I was tinkering with performance analyzer results in DAX Studio, until I decided just to strip out everything on the page that wasn’t essential to the stoplight chart.

One of the frustrating things about this is that I don’t know what I have to get the execution time down to in order for the table with all icons to be rendered before the snapshot is taken.

Thanks much!

Brian

AntrikshSharma · November 9, 2020, 4:09pm

I am assuming you won’t be able to provide the file so I will try to replicate the logic in Contoso database:

1: Sorry I wasn’t clear, I wanted to say full virtual table:
2: Look for time spent in FE and if there are any CALLBACKDATAID and how many rows SE is materializing

If you find CALLBACKDATAID, that is a good start and you know that SE is calling FE to do some complex computations. But it is not always a bad thing (although always aim to remove it), some time huge materialization can also cause problems.

BrianJ · November 9, 2020, 4:13pm

You know I would if I could. Unfortunately, it’s so chockablock with confidential data that it would be nearly impossible to anonymize. It’s also nightmarishly complex in terms of the ETL to transform the data from Dynamics in PBI (currently at over 1,800 lines of M… )

Please don’t go to a lot of trouble trying to build a mockup. Just a pseudo-code version of the approach you’re suggesting would be fine.

Thanks.

Brian

AntrikshSharma · November 9, 2020, 6:58pm

Worked on it for a while and pre-computing was causing more materialization in my case, but I think you could create a calculated column in your table? That might help.

AntrikshSharma · November 9, 2020, 7:10pm

Yup Yup Yup, Calculated column was blazing fast, why did I forget that!

y4jw5R

Calculated column, blanks can be taken care of:

Measure:

Codes were smaller than yours:

BrianJ =
VAR RedCircle = "🔴" //approval needed
VAR GreenCircle = "🟢" //approved
VAR BlackCircle = "⚫" //not in approval chain
VAR BlueCircle = "🔵" //Indicates debug problem – statement should not reach this level
VAR WhiteCircle = "⚪" //approval chain not yet initiated
VAR RedX = "❌" //rejected
VAR Result =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( Products[Category] ) = "Audio",
            SWITCH (
                SELECTEDVALUE ( Products[Brand] ),
                "Contoso", WhiteCircle,
                "Wide World Importers", WhiteCircle,
                "Northwind Traders",
                    SWITCH (
                        TRUE (),
                        [Total Sales] < 8321947, RedX,
                        [Total Sales] = 8321947, RedCircle,
                        [Total Sales] > 8321947, GreenCircle,
                        "Danger will robinson - Lost Creativity"
                    ),
                "Can't think anymore"
            ),
        CONTAINSSTRING ( SELECTEDVALUE ( Products[Category] ), "Toys" ),
            SWITCH (
                TRUE (),
                SELECTEDVALUE ( Products[Color] ) IN { "Red", "Blue", "Black", "Green" },
                    SWITCH (
                        TRUE (),
                        [Total Sales] > 100000, GreenCircle,
                        [Total Sales] > 400000, RedCircle,
                        [Total Sales] > 600000, RedX,
                        BlackCircle
                    )
            )
    )
RETURN
    Result

BrianJ · November 9, 2020, 7:34pm

@AntrikshSharma,

This is phenomenal - you are truly DAX Mozart.

Never occurred to me that calculated column would be substantially faster than a measure.

I’ll implement this with my data to confirm, but looking at your results I strongly suspect this will provide the speed needed to outrun the snapshot. Will definitely let you know.

Can you please attach your PBIX? I’d like do some testing on that in the service.

Thanks a ton for your help!

Brian

AntrikshSharma · November 9, 2020, 7:52pm

@BrianJ Here you go: For Brian.pbix (3.5 MB)

BrianJ · November 9, 2020, 8:22pm

@AntrikshSharma,

Sorry – one more question. Of the 300,000+ records currently in the system, only two dozen or less will need icons. From an optimization standpoint, what is the best way to handle this? Is it as simple as:

Icon Column =
IF( [ColumnX] = BLANK(), BLANK(), BrianJ )

Or is there a better way of handling this in terms speed of execution?

Thanks.

– Brian

AntrikshSharma · November 9, 2020, 8:30pm

@BrianJ I would uncheck blanks from the filters pane.

BrianJ · November 11, 2020, 8:15pm

@AntrikshSharma,

Works like a charm. Thanks so much for your help!

– Brian

you da man

AntrikshSharma · November 12, 2020, 4:24am

Awesome!