Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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
        )
Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    Hi Anonymous ,

     

    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
        )

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi Anonymous ,

     

    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
        )
  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi Anonymous 

    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.