CALCULATED TABLE USING MEASURE AS FILTER CRITERIA

[Anonymous]

Hi All, I am n newbie to DAX, with some experience in PL/SQL, stuck at situation am unable to resolve. Appreciate any suggestion from datanuts here, let me sum-up the situation using a proxy scenario;

I have created a slicer for the user to select ACCOUNT_FACT[ACCOUNT] from TABLE(2) ACCOUNT_FACT, which is then fed into a measure AC_SELECTED = SLELECTEDVALUE (ACCOUNT_FACT[ACCOUNT]), I want to pass on this value dynamically as filter criteria to create a new TABLE(3)  SALES_REP from existing TABLE(1) SALES_FACT

SALES_REP =

CALCULATETABLE(

DISTINCT (SELECTCOLUMNS(SALES_FACT,”SALE_GUYS”,SALES_FACT[SALES_REP]

   FILTER(SALES_FACT,

    SALES[ACCOUNT] = AC_SELECTED

)

However this doesn’t seem to work, I tried variable also. What I have realized in the process is that CALCULATETABLE function will not work using slicer values, as the tables are generated at the time of model load and hence any slicing post that will not dynamically refresh dependent tables. Is theer a work-around aproach ?

[Anonymous · Accepted solution]

@Anonymous -

It is true that Calculated Tables are created at refresh time, while Measures are calculated at query time. 

It seems that you are trying to filter on a column - I think you need an Account dimension table. You can create a relationship to each of your fact tables and use the Account from the new dimension table to filter the fact tables.

Hope this helps,

Nathan

[samabrew]

Could I possibly get a little more context here? I have the exact same issue.

[Anonymous]

@Anonymous 

 Works like magic! Thanks Nathan, that was very helpful, not just in getting around the problem, but also understanding how relationship can be configured to create a background for developing complex dependent functionalities. Cheers!