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.

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.

    • tkrupka's avatar
      tkrupka
      Resolver II
      6 years ago

      v-lid-msft 

       

      THANK YOU!,

       

      I have learned a lot based on your code!

  • tkrupka's avatar
    tkrupka
    Resolver II
    6 years ago

    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?