Table Filter does not work based on slicer selection

[Hosamby]

Dear members,

could you please help on the below issue

i'd added a what-if parameter on a slicer and used it to filter line chart visula based on the slicer selection (measure is added to calculate Aging average dymanically for the values less than or equal selected slicer value):

DP-M-Ag Excluded = CALCULATE(AVERAGE('R_DP_M_tbl'[DP-M Ag]), FILTER('R_DP_M_tbl','R_DP_M_tbl'[DP-M Ag] <= 'AgingLimitDP'[AgingLimitDP Value]))

where: 

DP-M-Ag Excluded : a meausre

'R_DP_M_tbl'[DP-M Ag]:  Aging column inside 'R_DP_M_tbl' table

AgingLimitDP : what-if parameter

it works fine, but when i tried to create a calculated table of excluded records "with aging values greated that slicer selected value" it doesn't work and there is no filtered applied , calculated table DAX as below:

ExcludedTable = CALCULATETABLE('R_DP_M_tbl',FILTER('R_DP_M_tbl','R_DP_M_tbl'[DP-M Ag] > AgingLimitDP[AgingLimitDP Value]))

i tied to ensure AgingLimitDP to get only one value by SELECTEDVALUE, VALUE and VALUES but it dosn't work.

also i tried to take AgingLimitDP[AgingLimitDP Value] in a new measuer and replace with the below expression but also doesn't work

ExcludedTable = CALCULATETABLE('R_DP_M_tbl',FILTER('R_DP_M_tbl','R_DP_M_tbl'[DP-M Ag] >[CollectedValue]))

where :

CollectedValue = SELECTEDVALUE(AgingLimitDP[AgingLimit]) 

or

CollectedValue = SELECTEDVALUE(AgingLimitDP[AgingLimit],0) 

or

CollectedValue = AgingLimitDP[AgingLimitDP Value]

noting that when manually adding value in CollectedValue measure (ex: CollectedValue = 100), the calculated table been filtered correctly 

Apprecite your support to highligh to me where is the issue comes from

[v-jtian-msft]

Hi,@Hosamby .I am glad to help you.

According to the description you tried to set filter values using the slicer and filter calculated tables created using dax through the slicer.
Unfortunately, the properties of calculated tables/calculated columns in Power BI are static, it is not possible to interact with the filters on the visualization page, so it is not possible to satisfy the need for dynamism, only when you refresh/create the calculated table or calculated column, their values change ( calculated tables and calculated columns take up memory, they are real existing static data, while measure is virtual dynamic data)
Measure can be interacted with dynamically, but it can only return a single value.

So using measure or calculated table in Desktop does not return a table dynamically (which is why you are having problems).
If you want to implement the use of measure to dynamically display the final results
Instead of an actual calculated table, you can use table visual.

You can try
Create a virtual table in measure and then use the virtual table to calculated or labeled data.
At this point the virtual table is part of the measure and receives the slicer's field information normally

Set measure = “The markers you set” in the filter field.

The slicer cannot affect the calculated table.
URL:

Solved: Calculated column based on slicer value - Microsoft Fabric Community
 

2. If the purpose of creating a calculated table is to display the final filtered table, try converting the data from the final filtered table into a string (using the CONCATENATEX function) using metrics and displaying it as such (after concatenation into a string, many of the subsequent calculations, such as summing, averaging, etc., are not possible, and only the final filtered results of the table can be displayed in the card).
URL:

CONCATENATEX function (DAX) - DAX | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Hosamby]

Hi @v-jtian-msft 

Thanks for your resposnse, it really helps to understand the root casue of faced issue.

As per your reccommendation i'd used CONCATENATEX function which resolved the issue as per below snapshoot and DAX

ExcludedMeasure = CONCATENATEX(FILTER(R_DP_M_tbl,R_DP_M_tbl[DP-M Ag] > AgingLimitDP[AgingLimitDP Value]),R_DP_M_tbl[Issue key] &", "& R_DP_M_tbl[Custom field (Main Impacted Services)] &", "& R_DP_M_tbl[Summary] &", "&R_DP_M_tbl[DP-M Ag],UNICHAR(10),R_DP_M_tbl[DP-M Ag],DESC)

but when i tried to create a virtual table to overcome the limitation of CONCATENATEX function and to have the result as a multi columned table , i got stucked with the expression error message below although i'm useing the same FILTER expression as an argument of SELECTCOLUMNS to represent the table , could you please advise why this happend?

VirtualTable = SELECTCOLUMNS(FILTER(R_DP_M_tbl,R_DP_M_tbl[DP-M Ag] > AgingLimitDP[AgingLimitDP Value]),"Issue Key",R_DP_M_tbl[Issue key],"Summary",R_DP_M_tbl[Summary],"Main",R_DP_M_tbl[Custom field (Main Impacted Services)],"Aging",R_DP_M_tbl[DP-M Ag])

Apprecite your support

Regards

Hossam

[v-jtian-msft]

Hi,@Hosamby .Thank you for your reply.
Virtual tables generally refer to the temporary tables defined in the measure/calculate column (parameter tables defined by means of var variables), which exist while the code is calculating.
The fact that you created the virtual table in measure is correct, but the problem is that the dynamic result returned in measure can be a scalar value, i.e. a fixed value that changes dynamically depending on the computing environment (aggregated result), but not a table (result with multiple rows and columns). (This is the problem you are currently having)
So I would suggest that you could present the multiple columns of this virtual table separately as aggregates in a table visual
You can try to create multiple measures, all of which have this table [VirtualTable], and each measure returns the aggregated value of only one of the columns in the table.
For example:

measure1 returns the final aggregated value of the [main] column.
measure2 returns the final aggregated value of the [Aging] column.

Combining Multiple Measures in a Table Visual
The final result of measure cannot be returned to the table, and it may be a good idea to use multiple measures in the same table visual to approximate the effect of dynamically displaying the table
All measures receive the same filtering (using the same virtual table)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Hosamby]

Hi Carson  @v-jtian-msft , Thank you for your support

I've implemented multiple measures in the virtual table visual as per your advice and it works and gave an approximate emulation of a normal dynamic table. i hope i done this is the correct way.

Thanks againg for your support

Regards

Hossam

[v-jtian-msft]

Hi,@Hosamby .Very happy to see that my advice has been of help to you.
Since the definition of the calculated table itself (which is static) doesn't support it changing dynamically according to the slicer, what we can achieve is to try to restore the output of the entire table through multiple measures (which is the step you're operating on).
Thank you for your support and understanding of power BI.