Forum Discussion

NickProp28's avatar
NickProp28
Icon for Post Partisan rankPost Partisan
3 years ago

A Measure take long load time/ fail

Dear community, 

 

My back-end contains millions lines of data. It loads quickly when it is all in one visualization table without any filters.

 

Now I need to identify which columns have been left blank by the user and point them out, so I have this measure.

C_Blank = 
SUMX(
    ADDCOLUMNS(
        SIT,
        "Count",
        var Direction = SIT[Direction]
        var INCO = SIT[INCOTERM]
        var res1 = COUNTROWS(
            FILTER(
                {SIT[Consol Number],SIT[ETD],SIT[ATD],SIT[ETA],SIT[Estimated Pickup],SIT[Interim Receipt Date],SIT[Actual Pickup]},
                NOT ISBLANK([Value])))
        var res2 = COUNTROWS(
            FILTER(
                {SIT[Consol Number],SIT[ETD],SIT[ATD],SIT[ETA],SIT[ATA],SIT[Estimated Pickup],SIT[Interim Receipt Date],
                 SIT[Actual Pickup],SIT[Estimated Delivery],SIT[Actual Delivery]},
                 NOT ISBLANK([Value])))
        var res3 = COUNTROWS(
            filter(
                {SIT[ATA],SIT[Estimated Delivery],SIT[Actual Delivery]},
                NOT ISBLANK([Value])))

        var res4 = COUNTROWS(
            FILTER({SIT[Estimated Pickup],SIT[Interim Receipt Date],SIT[Actual Pickup],SIT[Estimated Delivery],SIT[Actual Delivery]},
                  NOT ISBLANK([Value])))
        
         return  
        if (Direction = "Domestic" || SIT[Transport Mode] ="ROA", res4,
        if (Direction = "Export" && LEFT(INCO, 1) = "D" || (Direction = "Import" && LEFT(INCO, 1) = "D") , res2 , 
        if (Direction = "Export" && NOT(LEFT(INCO, 1) = "D") , res1,
        if (Direction = "Import" && NOT(LEFT(INCO, 1) = "D") , res3))))),
    [Count])

However, after I put this measure into the table, it took extremely long time to load, and sometimes it would fail to load.

Fail to load:

Could this measure be modified to speed up performance?

Appreciate any help provided!

Need Help

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Depending on how many additional columns the SIT table has which are not being used in the calculation, you could use SELECTCOLUMNS rather than ADDCOLUMNS to only pull in the columns necessary.

    It might also speed things up if you created a new column, Result Type, using the logic from your IF statement, e.g.

    Result Type =
IF (
    Direction = "Domestic"
        || SIT[Transport Mode] = "ROA",
    "res4",
    IF (
        Direction = "Export"
            && LEFT ( INCO, 1 ) = "D"
            || (
                Direction = "Import"
                    && LEFT ( INCO, 1 ) = "D"
            ),
        "res2",
        IF (
            Direction = "Export"
                && NOT ( LEFT ( INCO, 1 ) = "D" ),
            "res1",
            IF ( Direction = "Import" && NOT ( LEFT ( INCO, 1 ) = "D" ), "res3" )
        )
    )
)

    You might also be able to create this column in power query.

    Your measure could then do a SWITCH on the Result Type column to return the correct result.