Forum Discussion

tkrupka's avatar
tkrupka
Resolver II
6 years ago
Solved

Add mesaures when Column Removed

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

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

7 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

     

    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's avatar
      tkrupka
      Resolver II
      6 years ago

      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's avatar
        tkrupka
        Resolver II
        6 years ago

        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...)?