• Fumbling in the Dark with DevOps and Automation

    |

    In the past, I’ve been skeptical about how much things like PowerShell, Devops and Docker are relevant to me personally. It makes sense if you are writing application code. It makes sense if you are managing hundreds of servers.

    But I do Business Intelligence. How do you write unit tests for a report? Why do I need PowerShell when I can just hit Publish on Power BI Desktop? Do I really need Powershell if I manage 3 SQL Servers?

    This year, however, there have been a number of events that have been slowly changing my mind:

    I don’t know what I’m doing

    I’ve talked before about how automation is a relative term. But I’d like to do some true automation, I’d like to make a script like Cody’s where I can spin up a multi-server homelab with SQL Server, Sample databases and client tools all installed.

    And right now I have no idea what I’m Doing and I’m fumbling in the dark. I’ve made a github project and I’ve gotten Lability to create the virtual machines. I know I need to learn Desired State Configuration, and I can’t quite get it to work with Lability yet.

    And beyond that, I have no idea what I’m doing. And that’s okay. I suspect that this is a pain a lot of people run into with devops and why they put it off. The reason I write this is to remind people is that it’s okay to suck at something.

    Image result for adventure time suck

    I’ll keep y’all updated as I slowly make progress, fumbling in the dark.

  • 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

Recent Posts

Categories