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

[D_PBI]

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"
    )

[Anonymous]

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]

@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]

__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

[D_PBI]

@Anonymous  - thank you for your effort. Apologies for the delay in responding - just been busy.

Firstly, thanks for the tips about hard-coding. I totally agree and it is something that I avoid. The only reason why hard-coding is present in my example is so to reduce the number of functions in the my measure. The measure wasn't (unfortuantely, still isn't) working, so by trying to reduce the number of functions, I was hoping it would clean-up my query so to narrow down the error.

Okay, so to your effort. It's a step further than my effort due to the fact a DAX error is not being returned. However, the measure is returning a count of 14. 14 would be the number accounts that satisfy all the filtering barring then the __ageStatus variable being less than 5 years.

Take a look at the below screenshot. It shows a table of all accounts that meet the filtering (excluding the _Age in Years being less than five). From this table I would like a measure to return the count of 2. 2 being the number of accounts where the __Age in Years is less than five.

You, as I was previously, can return the count of the table above, however I need the measure to correctly return 2 (the number of accounts with a __Age in Years less than five).

Please can you amend your DAX so it works?
I've provided you a Kudos as I appreacite your help and effort, however I do need this measure working.

Thanks.

[Anonymous]

Hi there.

Would you mind posting some text data that I could copy into my PBI and work on it, please?

Best
D