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...
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
        )
mdaatifraza5556's avatar
mdaatifraza5556
Icon for Super User rankSuper 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.