Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tkrupka
Resolver II
Resolver II

CALCULATE has been used in a True/False ...

This produces the error "A function 'Calculate' has been used in a True/False expression that is used as a table filter expression.  This is not allowed."

CALCULATE (
            LASTNONBLANK ( RunData[DT_Level], 1 ),
            RunData[fk_GeneratorName_ID] = SelectedGenerator,
            RunData[RunData_Date] = CALCULATE (
                MAX (
                    RunData[RunData_Date]
                ),
                ALLEXCEPT (
                    Tanks,
                    Tanks[Tank_Name]
                )
            )
        )

I am trying to figure out something with this code:

LastTotalDayTankLevel = 
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] ) 
    )

VAR LastLevelDate =
    CALCULATE (
        MAX (
            RunData[RunData_Date]
        ),
        ALLEXCEPT (
            Tanks,
            Tanks[Tank_Name]
        )
    )
RETURN

    IF (
        ISINSCOPE ( Tanks[Tank_Name] ),
        CALCULATE (
            LASTNONBLANK ( RunData[DT_Level], 1 ),
            RunData[fk_GeneratorName_ID] = SelectedGenerator,
            RunData[RunData_Date] = LastLevelDate
        ),
        SUMX (
            SELECTCOLUMNS (
                FILTER ( 
                    ALL ( Tanks ), 
                    [fk_IBX_ID] = IBXID 
                ),
                    "tID", 
                    [fk_GeneratorName_ID]
            ),
            CALCULATE (
                LASTNONBLANK ( RunData[DT_Level], 1 ),
                FILTER (
                    RunData,
                    AND ( 
                        RunData[RunData_Date] = LastLevelDate,
                        AND (
                            RunData[fk_IBX_ID] = IBXID, 
                            RunData[fk_GeneratorName_ID] = [tID] 
                        )
                    )
                )
            )
        )
    )

The problem I am having is that by using VAR LastLevelDate I am only getting one date.  I need to be able to have the last date for each individual tank name which means many dates.

(i.e. Tank-x has last date of 8/1/2019, Tank-y has a date of 8/8/2019, Tank-z has a date of 8/15/2019.  As written the code only gives me 8/15/2019 so I only get a value for Tank-z)

 

 

PBIX file is here.

1 ACCEPTED SOLUTION

Figured out to make the compared value a Variable and it gets rid of the error.

View solution in original post

5 REPLIES 5
v-eachen-msft
Community Support
Community Support

Hi @tkrupka ,

 

You could use ALLEXCEPT() function to group by tank's name. Could you share your files? It is difficult to understand your table via the DAX.

 

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

@v-eachen-msft  The PBIX is linked at the bottom of the original post.

If the last date is all the same I get the following:

 

DAX Right.JPG

 

 

If the last date is not the same for all tanks, I get this:

 

DAX Wrong.JPG

 

 

There should always be a value for every tank.

Should I use a calculated column?  

 

Am I thinking correctly that a calculated column is updated with a dataset refresh?  If so, I may be able to figure out how to use this.

Figured out to make the compared value a Variable and it gets rid of the error.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.