Forum Discussion

carrascojg's avatar
carrascojg
Frequent Visitor
1 year ago
Solved

Filter temporal table with filter value

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!
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.