Add mesaures when Column Removed

[tkrupka]

I am trying to figure out how to set a sum of my measures when I remove one of the columns from my visual.

So if you look at the picture above, there is a measure titled 'LastDayTankLevel'.  Based on my formula, this is correct.

LastDayTankLevel = 

VAR
    SelectedTank =
        SELECTEDVALUE (
            Tanks[Tank_Name]
        )
RETURN

VAR
    SelectedGenerator =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[Tank_Name],
            SelectedTank
        )
RETURN

CALCULATE (
    LASTNONBLANK (
        RunData[DT_Level],
        1
    ),
    RunData[fk_GeneratorName_ID] = SelectedGenerator
)

But, if I remove the Tank_Name column, this does not add up all of the measures as I hope it would.  I'm assuming this is right because now my VAR 'SelectedTank' is now NULL, which gives me an output of:

What I would really like to see is the SUM of LastDayTankLevel for the entire IBX.  PBIX

[v-lid-msft]

Hi @tkrupka ,

Yes, the measure return blank because the var look up a value in unfilted column, I noticed that the IBX has a relation ship with RunData, so we can create a measure to meet your requirement

LastDayTankLevel2 =
VAR t =
    LOOKUPVALUE ( IBXs[IBX_ID], IBXs[IBX_Name], SELECTEDVALUE ( IBXs[IBX_Name] ) )
RETURN
    CALCULATE ( LASTNONBLANK ( RunData[DT_Level], 1 ), RunData[fk_IBX_ID] = t )

or if you want to show different value depens on the column in table, you can use this measure

LastDayTankLevel3 =
VAR SelectedTank =
    SELECTEDVALUE ( Tanks[Tank_Name] )
VAR SelectedGenerator =
    LOOKUPVALUE ( Tanks[fk_GeneratorName_ID], Tanks[Tank_Name], SelectedTank )
VAR t =
    LOOKUPVALUE ( IBXs[IBX_ID], IBXs[IBX_Name], SELECTEDVALUE ( IBXs[IBX_Name] ) )
RETURN
    IF (
        ISINSCOPE ( Tanks[Tank_Name] ),
        CALCULATE (
            LASTNONBLANK ( RunData[DT_Level], 1 ),
            RunData[fk_GeneratorName_ID] = SelectedGenerator
        ),
        CALCULATE ( LASTNONBLANK ( RunData[DT_Level], 1 ), RunData[fk_IBX_ID] = t )
    )

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

[tkrupka]

@v-lid-msft 

Thanks for the response, this did give me the last nonblank for the IBX, but I need it to be the sum of all of the tanks last nonblank value within that IBX.

So in the case of DC11, it would be 45,808 instead of 5788 which is only the value of R3.

[tkrupka]

If I add a calculated column on my Tanks table with the following formula I get this:

INDEX = 
RANKX (
    FILTER (
        Tanks,
        EARLIER ( Tanks[fk_IBX_ID] ) = Tanks[fk_IBX_ID]
            && EARLIER ( Tanks[Day_Tank] ) = Tanks[Day_Tank]
    ),
    Tanks[Tank_ID],
    ,
    ASC
)

And if I use the following formula I get the number of day tanks per IBX:

DayTanksPerIBX = 
VAR
    SelectedIBX =
    SELECTEDVALUE ( RunData[fk_IBX_ID] )
RETURN

CALCULATE (
    DISTINCTCOUNT ( Tanks[Tank_ID] ),
    Tanks[fk_IBX_ID] = SelectedIBX,
    Tanks[Day_Tank] = TRUE()
)

Is there a way to do a lookup for Index 1's last nonblank tank level then add it two Index 2's etc.... up to the max number for the IBX. (8 for DC11, 7 for DC5, etc...)?

[tkrupka]

I tried to use @Greg_Deckler blog post here about DAX equivalent For and While loops.

I tried to use my DayTanksPerIBX measure as the maximum value for

VAR __loopTable = GENERATESERIES(1,__n)

But it gives me an error saying that no values can be blank.

[tkrupka]

OK, so I thought I found the mistake in my code, but it still didn't give me the results I was looking for.

My file is here.

So, once again, I tried @Greg_Deckler blog for a FOR loop equivalent with this code:

ForLoop = 

VAR
    SelectedIBX =
    SELECTEDVALUE ( RunData[fk_IBX_ID] )

VAR
    DTsPerIBX =
    CALCULATE (
        DISTINCTCOUNT ( Tanks[Tank_ID] ),
        Tanks[fk_IBX_ID] = SelectedIBX,
        Tanks[Day_Tank] = TRUE()
    )

VAR
    __n = DTsPerIBX

VAR
    __totalGallons = 0

VAR
    __loopTable =
        GENERATESERIES (
            1,
            __n
        )

VAR
    __loopTable1 =
        ADDCOLUMNS (
            __loopTable,
            "Total Gallons",
            __totalGallons +
            SUMX (
                FILTER (
                    __loopTable,
                    [Value] <= EARLIER([Value]
                    )
                ),
            [Value]
            )
        )

VAR 
    __max =
        MAXX (
            __loopTable1,
            [Value]
        )
RETURN

MAXX (
    FILTER (
        __loopTable1,
        [Value] = __max
    ),
    [Total Gallons]
)

But I received this error when I added it to my visual.

If I try the following code:

ForLoop = 

VAR
    SelectedIBX =
    SELECTEDVALUE ( RunData[fk_IBX_ID] )

VAR
    DTsPerIBX =
    CALCULATE (
        DISTINCTCOUNT ( Tanks[Tank_ID] ),
        Tanks[fk_IBX_ID] = SelectedIBX,
        Tanks[Day_Tank] = TRUE()
    )

VAR
    __n = DTsPerIBX

VAR
    __totalGallons = 0
RETURN

CALCULATE (
    VALUE(__totalGallons)
)

I get an output of 0 which is expected,

And if instead of getting the value of __totalGallons and put in _DTsPerIBX I also get the expected value.

So why then if I put __n (__DTsPerIBX) into the GENERATESERIES formula am I getting the error?  I would have thought that the generator series would have gone from 1 to 8 if you look at DC11?  Or, is the problem being caused when __DTPerIBX = NULL/0?

[v-lid-msft · Accepted solution]

Hi @tkrupka ,

Based on your addtional information, we can try to use the following measure and do not need to create other calculate column.

LastDayTankLevel2 =
VAR IBXID =
    LOOKUPVALUE ( IBXs[IBX_ID], IBXs[IBX_Name], SELECTEDVALUE ( IBXs[IBX_Name] ) )
RETURN
    SUMX (
        SELECTCOLUMNS (
            FILTER ( ALL ( Tanks ), [fk_IBX_ID] = IBXID ),
            "tID", [fk_GeneratorName_ID]
        ),
        CALCULATE (
            LASTNONBLANK ( RunData[DT_Level], 1 ),
            FILTER (
                RunData,
                AND ( RunData[fk_IBX_ID] = IBXID, RunData[fk_GeneratorName_ID] = [tID] )
            )
        )
    )

or the two in one formula version

LastDayTankLevel3 = 
VAR SelectedTank =
    SELECTEDVALUE ( Tanks[Tank_Name] )
VAR SelectedGenerator =
    LOOKUPVALUE ( Tanks[fk_GeneratorName_ID], Tanks[Tank_Name], SelectedTank )
VAR IBXID =
    LOOKUPVALUE ( IBXs[IBX_ID], IBXs[IBX_Name], SELECTEDVALUE ( IBXs[IBX_Name] ) )
RETURN
    IF (
        ISINSCOPE ( Tanks[Tank_Name] ),
        CALCULATE (
            LASTNONBLANK ( RunData[DT_Level], 1 ),
            RunData[fk_GeneratorName_ID] = SelectedGenerator
        ),
        SUMX (
            SELECTCOLUMNS (
                FILTER ( ALL ( Tanks ), [fk_IBX_ID] = IBXID ),
                "tID", [fk_GeneratorName_ID]
            ),
            CALCULATE (
                LASTNONBLANK ( RunData[DT_Level], 1 ),
                FILTER (
                    RunData,
                    AND ( RunData[fk_IBX_ID] = IBXID, RunData[fk_GeneratorName_ID] = [tID] )
                )
            )
        )
    )

If the result is still not match your expeted one, just  figure it out.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tkrupka]

@v-lid-msft 

THANK YOU!,

I have learned a lot based on your code!