Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
6 years ago

Counting rows based on multiple filters - what's wrong with this DAX measure?

Hi all,

I am trying to return a single number from the account table based on the filtering in the below DAX code. I feel the code is pretty self explanatory so I shalln't explain it further. 
I am getting the following error message.
"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

Please can someone explain to me what is wrong with the below DAX? 

Thanks in advance.

__Dissolved Portfolio Companies =
VAR __Dissolved_Date = SELECTEDVALUE ( account[Dissolved Date] )
VAR __Incorporation_Date = SELECTEDVALUE ( account[Incorporation Date] )
VAR __Age_in_Years =
    IF (
        ISBLANK ( __Dissolved_Date ),
        IF ( ( QUOTIENT ( DATEDIFF ( __Incorporation_Date, DATE ( 2020, 12, 31 ), DAY ), 365 ) ) < 5, "Return", "Dont" ),
        IF ( ( QUOTIENT ( DATEDIFF ( __Incorporation_Date, __Dissolved_Date, DAY ), 365 ) ) < 5, "Return", "Dont" )
       )
RETURN
    CALCULATE (
        COUNT ( account[accountid] ),
        ALL ( account ),
        account[HE-BCI Category] IN { "1 Spin-off", "2 Formal Spin-off", "3 Staff Start-up", "4 Graduate Start-up" },
        account[Status Reason] IN { "Portfolio Dissolved (CESF)", "Ex-Portfolio Dissolved (CESF)" },
        account[Incorporation Date] <= DATE ( 2014, 12, 31 ),
        __Age_in_Years = "Return"
    )

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    The message says it all:
    Each True/False expressions used as a table filter expression must refer to exactly one column.

    This:

    __Age_in_Years = "Return"

    does not refer to any column in any table.

    Best
    D
    • D_PBI's avatar
      D_PBI
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Anonymous thanks for your response.
      Yes, I could see it is the bit you've identified that is causing the problem (by commenting in/out different code lines).

      Are you able to construct the measure correctly so it returns what I require?
      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        __Dissolved Portfolio Companies =
VAR __dissolvedDate = SELECTEDVALUE ( account[Dissolved Date] )
VAR __incorporationDate = SELECTEDVALUE ( account[Incorporation Date] )
VAR __veryLastDateInCurrentYear = DATE ( YEAR( TODAY() ), 12, 31 )
// This should be calculated as well, not hard-coded, since
// the current your could be a leap one...
VAR __numOfDaysInCurrentYear = 365
VAR __ageLimitExclusive = 5
VAR __effectiveDate =
	COALESCE( __dissolvedDate, __veryLastDateInCurrentYear )
VAR __daysFromIncorpDateToEffectiveDate =
	DATEDIFF ( 
		__incorporationDate,
		__effectiveDate, 
		DAY
	)
VAR __ageStatus =
        IF ( 
        	QUOTIENT (
        		__daysFromIncorpDateToEffectiveDate,
        		__numOfDaysInCurrentYear ) < __ageLimitExclusive,
        	"Return",
        	"Dont"
        )
VAR __result =
    CALCULATE (
    
    	// This COUNT(...) should be a basic measure already defined
    	// in the model.
        COUNT ( account[accountid] ), 
        
        // If you do not wrap the conditions below in KEEPFILTERS,
        // they'll lose any filters that have aleady been set on them.
        account[HE-BCI Category] IN { 
        	"1 Spin-off",
        	"2 Formal Spin-off",
        	"3 Staff Start-up",
        	"4 Graduate Start-up"
        },
        account[Status Reason] IN {
        	"Portfolio Dissolved (CESF)",
        	"Ex-Portfolio Dissolved (CESF)"
        },
        
        // Also, be aware that if you hard-code date like this
        // it'll not be adjustable. Maybe you should obtain this
        // date from a table? Not saying it's wrong... maybe
        // you've got a legitimate reason to do so...
        account[Incorporation Date] <= DATE ( 2014, 12, 31 ),
        
        ALL ( account )
    )	
RETURN
	if( __ageStatus = "Return", __result )

         

        Maybe the above...

         

        Best

        D