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

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'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?