passing variable selected from slicer to the table expression in DAX

[radhasri_0505]

selectedJob = SELECTEDVALUE(EMP[JOB]) -- This is Measure

Table1 = FILTER(SELECTCOLUMNS(EMP,"@EMPNAME",EMP[ENAME],"@JOB",EMP[JOB]),[@JOB]="CLERK")

This is successfully creating the table.However when I am replacing CLERK with SELECTEDVALUE(EMP[JOB]). This is not working. Any reason. From last 4 days I am struggling with this problem. Please suggest is there any alternate for this?

[Anonymous · Accepted solution]

Hi @radhasri_0505 ,

What you are asking for is not possible as Power BI is currently designed. Slicers cannot affect calculated tables. Calculated columns and calculated tables are evaluated once when the data is first loaded and are static until the data is refreshed.

However, you can get the table visual you want in a much simpler manner by writing the appropriate measure and putting that in the table instead of defining an entirely separate table.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[radhasri_0505]

What is the Solution for this? Is there any way to achieve this?

[danextian]

Hi @radhasri_0505 

While you may be able to create a calculated table from existing measures, a calculated table doesn't refresh when the resulting value of a mesure changes.  As per documentation: Calculated tables are always imported, and their data is refreshed when you refresh the tables. 

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#calculated-tables

My suggestion would be to include all EMP[JOB] items in your calculated table and not just a single one and then use the column in the calculated table that contains EMP[JOB] in a slicer.

[Anonymous]

@radhasri_0505 Because measure values can not be accessed into columns or tables creation.

Know the difference between measures and calculated column or table

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/#:~:text=The%20difference%20is....

Accept the solution if this is helpful.