topic Re: Filter temporal table with filter value in Developer

Filter temporal table with filter value

carrascojg — Thu, 05 Dec 2024 20:11:30 GMT

Hi All,

How cam i filter a temporal table with a filter values:

For example:

I have this temporal table T_Temp and in screen i have a filter for Market (get values from Marketing table. Eg: EMEA, Americas, India, etc)

T_Temp =

FILTER(

CALCULATETABLE (

SUMMARIZE(

'Facility Cost'

,'Facility Cost'[City]

, 'Facility Cost'[Market]

, "Cost", SUM('Facility Cost'[Facilities Costs])

)

, 'Facility Cost'[City] <> ""

&& 'Facility Cost'[Market] = VALUE FROM FILTER

)

I only made this filter works on that table when i use a string values like "EMEA" as a "VALUE FROM FILTER", but when i use

SELECTEDVALUE('Marketing'[Market]), it doesn't work.

Somebody can help with this?

Thanks!

Re: Filter temporal table with filter value

lbendlin — Fri, 06 Dec 2024 01:06:47 GMT

Read about SUMMARIZECOLUMNS and TREATAS. These are preferred over SUMMARIZE and FILTER.

Re: Filter temporal table with filter value

Anonymous — Fri, 06 Dec 2024 07:36:43 GMT

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @carrascojg

Based on your description, I suspect you might be testing the DAX you mentioned in a calculation table.

However, currently, there is no DAX that can capture slicer values in a calculation table or calculated column.

Below are the screenshots of the relevant answers:

For more details, please refer to:

Solved: Using Slicer values in a calculated column - Microsoft Fabric Community
Solved: Calculated column based on slicer value - Microsoft Fabric Community

Therefore, I suggest using the DAX you intend to use in a measure:

1.Here is my sample data:

2.This is my optimised test measure:

MEASURE = VAR cc = FILTER ( SUMMARIZE ( 'Facility Cost', 'Facility Cost'[City], 'Facility Cost'[Market], "Cost", SUM ( 'Facility Cost'[Facilities Costs] ) ), 'Facility Cost'[City] <> BLANK () && 'Facility Cost'[Market] IN VALUES ( Marketing[Market] ) ) RETURN SUMX ( cc, [Cost] )

3.The final result is as follows:

Please find the attached pbix relevant to the case.

Of course, if you have any new discoveries or questions, please feel free to get in touch with us.

Best Regards,

Leroy Lu

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

Re: Filter temporal table with filter value

carrascojg — Wed, 11 Dec 2024 00:42:26 GMT

Thank you Leroy, excelent workaround.