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 'LastDayTankLeve...
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.

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

    • tkrupka's avatar
      tkrupka
      Resolver II
      6 years ago

      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.

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        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.