Return Filter from Switch Statement DAX

[Anonymous]

Hi,

I have following code

Transactions Last FY = 
    VAR _selectedCurrency = SELECTEDVALUE('Selected Currency'[Currency Type])
    VAR _selectedPeriod = SELECTEDVALUE('Selected Period'[Period Type])
    
    VAR _filter = 
        SWITCH(_selectedPeriod,
               "Last Month", 
                FILTER ( ALL ('Date Table'[Is Last Year Last Month]), 'Date Table'[Is Last Year Last Month] = TRUE() ),
                "Current Month", 
                FILTER( ALL ( 'Date Table'[Is Last Year Current Month] ), 'Date Table'[Is Last Year Current Month] = TRUE() ),
                "Quarter to Date", 
                FILTER( ALL ( 'Date Table'[Is In Last FQTD] ), 'Date Table'[Is In Last FQTD] = TRUE() ),
                "Last Month Year to Date", 
                FILTER( ALL ( 'Date Table'[Is Complete Fiscal Month of Last FY] ), 'Date Table'[Is Complete Fiscal Month of Last FY] = TRUE() ),
                "Year to Date", 
                FILTER ( ALL ( 'Date Table'[Is In Last FYTD] ), 'Date Table'[Is In Last FYTD] = TRUE() )
            )
    RETURN 
        SWITCH(
            _selectedCurrency, 
            "Group Currency", CALCULATE ( [Transactions Last FY (Local Currency)], _filter ),
            "Local Currency", CALCULATE ( [Transactions Last FY (Group Currency)], _filter ),
            "Select a single currency type" )

This throws error True/False expression must refer exactly one column.

Where is the error in the code?

Thanks

[OwenAuger · Accepted solution]

Hi @Anonymous 

The main issue here is the variable _filter and how it is used.

1. SWITCH (and IF) cannot return table values, only scalar values.
   Within the expression for _filter, each of the possibilities within the SWITCH function evaluates to a one-row/one-column table. It just so happens that DAX converts 1x1 tables to a scalars in cases where a scalar is expected. Since SWITCH can only return a scalar, the result is a scalar TRUE or FALSE value. If any of these tables had more than one row or column, an error would be returned.
2. Since _filter is a scalar value equal to TRUE or FALSE, it cannot be used as a filter argument of CALCULATE. If a boolean expression is provided as a filter argument, it must contain a column reference, otherwise it cannot be interpreted as a filter. This causes the error you mentioned.

How to fix this?

First, as a general recommendation, you may want to create sub-measures, to preserve your sanity 🙂

Anyhow, here are some ideas:

Option 1: Create a conditional filter table using FILTER instead

Within the _filter variable, create a crossjoin of all combinations of the 5 'Date Table' boolean columns, then filter this table depending on _selectedPeriod. Also, you should add REMOVEFILTERS ( 'Date Table' ) as an argument within CALCULATE.

Also, for a boolean expression, <boolean expression> = TRUE () is equivalent to <boolean expression>.

So rather than writing 'Date Table'[Is Last Year Last Month] = TRUE (), you can just write 'Date Table'[Is Last Year Last Month].

I didn't bother creating any sub-measures here but this illustrates the principle:

Transactions Last FY =
VAR _selectedCurrency =
    SELECTEDVALUE ( 'Selected Currency'[Currency Type] )
VAR _selectedPeriod =
    SELECTEDVALUE ( 'Selected Period'[Period Type] )
VAR _filter =
    FILTER (
        ALL (
            'Date Table'[Is Last Year Last Month],
            'Date Table'[Is Last Year Current Month],
            'Date Table'[Is In Last FQTD],
            'Date Table'[Is Complete Fiscal Month of Last FY],
            'Date Table'[Is In Last FYTD]
        ),
        SWITCH (
            _selectedPeriod,
            "Last Month", 'Date Table'[Is Last Year Last Month],
            "Current Month", 'Date Table'[Is Last Year Current Month],
            "Quarter to Date", 'Date Table'[Is In Last FQTD],
            "Last Month Year to Date", 'Date Table'[Is Complete Fiscal Month of Last FY],
            "Year to Date", 'Date Table'[Is In Last FYTD]
        )
    )
RETURN
    CALCULATE (
        SWITCH (
            _selectedCurrency,
            "Group Currency", [Transactions Last FY (Group Currency)],
            "Local Currency", [Transactions Last FY (Local Currency)],
            "Select a single currency type"
        ),
        _filter,
        REMOVEFILTERS ( 'Date Table' )
    )

Option 2: Use SWITCH to choose between 5 different expressions depending on Period Type.

Doing it this way, I would recommend a sub-measure Transactions Last FY Base:

Transactions Last FY Base =
VAR _selectedCurrency =
    SELECTEDVALUE ( 'Selected Currency'[Currency Type] )
RETURN
    SWITCH (
        _selectedCurrency,
        "Group Currency", [Transactions Last FY (Group Currency)],
        "Local Currency", [Transactions Last FY (Local Currency)],
        "Select a single currency type"
    )

Transactions Last FY =
VAR _selectedPeriod =
    SELECTEDVALUE ( 'Selected Period'[Period Type] )
RETURN
    SWITCH (
        _selectedPeriod,
        "Last Month",
            CALCULATE (
                [Transactions Last FY Base]
                'Date Table'[Is Last Year Last Month],
                REMOVEFILTERS ( 'Date Table' )
            ),
        "Current Month",
            CALCULATE (
                [Transactions Last FY Base],
                'Date Table'[Is Last Year Current Month],
                REMOVEFILTERS ( 'Date Table' )
            ),
        "Quarter to Date",
            CALCULATE (
                [Transactions Last FY Base],
                'Date Table'[Is In Last FQTD],
                REMOVEFILTERS ( 'Date Table' )
            ),
        "Last Month Year to Date",
            CALCULATE (
                [Transactions Last FY Base],
                'Date Table'[Is Complete Fiscal Month of Last FY],
                REMOVEFILTERS ( 'Date Table' )
            ),
        "Year to Date",
                [Transactions Last FY Base],
                'Date Table'[Is In Last FYTD],
                REMOVEFILTERS ( 'Date Table' )
            )
    )

Option 3: Use Calculation Groups?

You could transfer the logic of the various SWITCH statements to Calculation Groups, certainly for the Period Type selection.

It's quite possible I've mis-interpreted something, so please post back if these aren't giving the expected results!

Regards,

Owen

[rivthebest]

Hi Owen,

I am having a similar problem. Let me narrate the same.

data: https://www.icmagroup.org/membership/List-of-principal-delegates-2/

In Power Query (Get & Transform) it will yield 4 text based columns namely: Company, First Name, Surname, Member Jurisdiction. I created a new column having named "Full Name" by concatenating First Name and Surname and delete First Name and Surname from the dataset. There are total 634 records (record count might vary as it is live URL )

My Objective:

Filtering the company name per Member Jurisdiction by using the filtering condition on the column Company. Here is an example:

Total Count of Member Banks of Type LLP = 
CALCULATE( 
     COUNT( ICMA_Members[Company] ),
     FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM("LLP") ) )
)

Now there are other filtering conditions like LLC, PLC, Limited, Ltd, B.V. and for each of them if I create a Measure then it would be a big lists of same type of measures. So I thought of collating all these filtering activity in a single DAX measure like the following:

Total Count of Member Banks of Each Type = 

VAR Filter_TypeAG = 
FILTER( ICMA_Members, 
            (
                CONTAINSSTRING( ICMA_Members[Company], " AG "  ) ||
                CONTAINSSTRING( ICMA_Members[Company], " AG"  )
            )
)

VAR Filter_TypeBV = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM("B.V.") ) )

VAR Filter_TypeGmbH = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM( "GmbH" ) ) )

VAR Filter_TypeLimited = 
FILTER( ICMA_Members, 
        (
            CONTAINSSTRING( ICMA_Members[Company], TRIM("Limited") ) || 
            CONTAINSSTRING( ICMA_Members[Company], TRIM("Ltd") ) 
        )
)

VAR Filter_TypeLLC = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM("LLC") ) )

VAR Filter_TypeLLP = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM("LLP") ) )

VAR Filter_TypembB = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM("mbB") ) )

VAR Filter_TypeNV = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], TRIM("N.V.") ) )

VAR Filter_TypePLC = 
FILTER( ICMA_Members, 
            ( 
              CONTAINSSTRING( ICMA_Members[Company], "PLC" ) || 
              CONTAINSSTRING( ICMA_Members[Company], "p.l.c" )
            ) 
        )

VAR Filter_TypeSA = 
FILTER( ICMA_Members,
            (
                CONTAINSSTRING( ICMA_Members[Company], TRIM("S.A.") ) ||
                CONTAINSSTRING( ICMA_Members[Company], " SA " ) ||
                CONTAINSSTRING( ICMA_Members[Company], " SA" )
            )
     )

VAR Filter_TypeSpA = 
FILTER( ICMA_Members, 
            (
                CONTAINSSTRING( ICMA_Members[Company], " S.p.A." ) || 
                CONTAINSSTRING( ICMA_Members[Company], " SpA" ) ||
                CONTAINSSTRING( ICMA_Members[Company], " S.P.A." )
            ) 
        )        

VAR Filter_TypeSE = 
FILTER( ICMA_Members, CONTAINSSTRING( ICMA_Members[Company], " SE " ) )

VAR Filter_TypeOthers = 
FILTER( ICMA_Members,
           NOT( 
               (
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "Limited" ) ) || 
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "Ltd" ) ) || 
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "LLP" ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], "PLC" ) ||
                   CONTAINSSTRING( ICMA_Members[Company], "p.l.c" ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "B.V." ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "N.V." ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "S.A." ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( " SA " ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], " S.p.A." ) ||
                   CONTAINSSTRING( ICMA_Members[Company], " SpA" ) ||
                   CONTAINSSTRING( ICMA_Members[Company], " S.P.A" ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "AG" ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "GmbH" ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], TRIM( "mbB" ) ) ||
                   CONTAINSSTRING( ICMA_Members[Company], " SE  " )
                )
            ) 
)    

RETURN
CALCULATETABLE( 
    VALUES( 
        SWITCH( 
            TRUE(),
            Filter_TypeAG, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeAG ),
            Filter_TypeBV, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeBV ),
            Filter_TypeGmbH, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeGmbH ),
            Filter_TypeLimited, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeLimited ),
            Filter_TypeLLC, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeLLC ),
            Filter_TypeLLP, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeLLP ),
            Filter_TypembB, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypembB ),
            Filter_TypeNV, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeNV ),
            Filter_TypePLC, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypePLC ),
            Filter_TypeSA, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeSA ),
            Filter_TypeSE, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeSE ),
            Filter_TypeSpA, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeSpA ),
            Filter_TypeOthers, CALCULATE( COUNT( ICMA_Members[Company] ), Filter_TypeOthers )
        )
    )
 )

The corresponding error message I am seeing at the bottom is pasted as a screenshot:

Please advise me which of the options mentioned by you in the previous step would be applicable for my case or do I have plan it in a different way.

The part 2 of the problem is placing the output of each of these counts to be placed either in separate card visual or mult-row card visual where Member Jurisdiction as report filter. The screenshot of the planned visual is attached below:

Please help me out.

Regards Riv

[OwenAuger]

Hi @rivthebest 

Just reading through your requirements, I would suggest a different approach.

Given that you want to classify each company as AG, BV etc, I would suggest adding a text column to your table to store these values, called "Type" or similar. This will given better performance than calculating these on the fly in measure(s), and it should be easier to create the visuals you are wanting.

I would recommend adding this Type column in Power Query, but you could also create a DAX calculated column (using similar code to what you have already written).

Please post back if needed, and a working PBIX would help (if you can share it).

Regards,

Owen

[rivthebest]

Thank you Owen,

Let me try the steps you mentioned and I will post my progress on that for sure.

Regards Riv

[Anonymous]

Hi @OwenAuger ,

I want to now improve on this logic and give consumer a choice of choosing fiscal years to compare.

I have previously implemented your suggestion 1, how can I add a dynamic year in the logic?

Thanks

Evan

[Anonymous]

Hi @OwenAuger ,

Thank you fo your reply.

Your explnations are very helpful.

Thanks