• DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

    |

    ,

    Promotion: Use code DAXERROR  to save 10% off my course. Module 1 is free.

    Whenever you start trying to use more complicated filters in the CALCULATE or CALCULATETABLE functions in DAX, you may start to get the following error:

    A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

    image

    The function in single quotes may vary. Instead of MAX, it could be SUM, MIN, AVERAGE or nearly anything. Sometimes, you may not even be using a function and the error will just say CALCULATE is the problem:

    A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

    image

    What causes this error?

    The error is caused by using a TRUE/FALSE expression, something that evaluates to TRUE or FALSE, to filter the table in a way that CALCULATE or CALCULATETABLE doesn’t support.  So the error is saying you can’t use a boolean comparison to filter your table except in very specific circumstances.

    The following comparisons are not supported:

      1. Comparing to a column to a measure. SalesHeader[TerritoryID] = [LargestTerritory]
      2. Comparing a column to a an aggregate value. SalesHeader[TerritoryID] = MAX(TerritoryID[TerritoryID]])
      3. Comparing a column to a What-If parameter. SalesHeader[TerritoryID] =

    TerritoryParameter[TerritoryParameter Value]

    In fact, you only have three options if you want to filter a column in a CALCULATE/CALCULATETABLE function:

    1. Compare the column to a static value. SalesHeader[TerritoryID] = 6
    2. Use variables to create a static value. VAR LargestTerritory = MAX(SalesHeader[TerritoryID])
    3. Use a FILTER function instead of a true/false expression. FILTER(SalesHeader, SalesHeader[TerritoryID] = [LargestTerritory])

    This is because CALCULATE was designed for safety and performance. Complex row based comparisons can dramatically affect performance. So, in order to do more complex comparisons, you have to take the safety feature off and use the FILTER function.

    How do I fix it?

    In order to fix the issue, wrap your expression in the FILTER function. To use the FILTER function, you need to pass in the table you want to filter, and then a TRUE/FALSE expression to determine which rows get return. So, let’s say we had the following code:

    CALCULATE (
    SUM ( SalesHeader[TotalDue] ),
    SalesHeader[TerritoryID] = [LargestTerritory]
)

    to use the FILTER function, we would use this:

    CALCULATE (
    SUM ( SalesHeader[TotalDue] ),
    FILTER ( ALL ( SalesHeader[TerritoryID] ), SalesHeader[TerritoryID] =    [LargestTerritory] )
)

    The ALL function isn’t strictly necessary, but normally when we filter a single column in a CALCULATE function, it will undo any existing filters on that column. We use ALL here to replicate that behavior. In order to understand the specifics better, check out this article at sqlbi.com

  • Getting Kubernetes and Containers to “click” for me

    |

    Today I had the pleasure of co-hosting the GroupBy Conference. Part of that involved co-hosting as Anthony Nocentino present on Kubernetes. His talk was based on his Pluralsight video on the same topic. After watching his presentation, Kubernetes finally clicked for me. I think I get it.

    Before you can get what Kubernetes is about, you need to understand one layer lower and get what containers are about. Aaron Nelson has written a great article on setting up SQL in containers in 5 lines of code. This helped me see how quick and easy it is to spin up a container. Additionally, I see how useful it is to be able to set up a container, kill it and spin up a new one, all in a matter of seconds.

    Once you start playing around with containers, you realize you need some way to control and organize them. If you are going to treat them like cattle, not pets, then you need to higher a cattle wrangler. Kubernetes is that cattle wrangler. Or should I call it a kattle wrangler?

    I wrote last week about how The Phoenix Project totally altered the way I think about work. It also altered the way I think about deployments and devops. To go fast, to make 10 deploys per day, you need to remove humans as much as possible. You need infrastructure as code. Kubernetes turns your datacenter into code.

    I still have some reservations about SQL Server Big Data edition, and I have to wonder when Kubernetes is overkill. But when you need to do dozens of deployments, or blue-green deployments, or implement stateless microservices, it’s a total no-brainer.

Recent Posts

Categories