The function expects a table expression for argument '', but a string or numeric expression was used

[JIli]

Hi there. With the following DAX I am trying to create two temporary tables: accive assets and inactive assets, and then  count all assets that do appear in "inactive" but not in "active" anymore (it is clear tham before they became inactive they were active at some point). I get the error: The function expects a table expression for argument '', but a string or numeric expression was used. Would be grateful for your help.

Churned assets =

VAR active_assets = CALCULATE(DISTINCT('CA'[AssetName]), FILTER ('CA','CA'[AssetState] = "Active") )

VAR inactive_assets = CALCULATE(DISTINCT('CA'[AssetName]), FILTER('CA','CA'[AssetState] = "Inactive") )

RETURN

    CALCULATE(

        COUNT('CA'[AssetName]),

       'CA'[AssetName] IN inactive_assets,

       'CA'[AssetName] NOT IN active_assets

        )

[mdaatifraza5556]

Hi @JIli 

can you please try the below dax.

Churned Assets =
VAR active_assets =
                      FILTER (
                           VALUES ( 'CA'[AssetName] ),
                          CALCULATE ( MAX ( 'CA'[AssetState] ) = "Active" )
                     )
VAR inactive_assets =
                    FILTER (
                           VALUES ( 'CA'[AssetName] ),
                           CALCULATE ( MAX ( 'CA'[AssetState] ) = "Inactive" )
                   )
VAR churned_assets =
                    EXCEPT ( inactive_assets, active_assets )

RETURN
COUNTROWS ( churned_assets )

If this answers your questions, Kindly accept it as a solution and give kudos.

[MFelix · Accepted solution]

Hi @JIli ,

You are calculating in each of the variables a number and that cannot be used in the filter of the calculate try this instead:

Churned assets =
VAR active_assets = CALCULATETABLE(VALUES('CA'[AssetName]), FILTER ('CA','CA'[AssetState] = "Active") )
VAR inactive_assets = CALCULATETABLE(VALUES('CA'[AssetName]), FILTER('CA','CA'[AssetState] = "Inactive") )

RETURN
    CALCULATE(
        COUNT('CA'[AssetName]),
       'CA'[AssetName] IN inactive_assets,
       'CA'[AssetName] NOT IN active_assets
        )