Forum Discussion

catnapwat's avatar
catnapwat
Icon for Helper I rankHelper I
6 years ago
Solved

Secondary data table not filtering from slicer

I have a main fact table which has a dd mmmm yy Date format, and a full Date table (with hierarchy) which I use as my date filter.   I also have a secondary table which has an unusual date format t...
Need Help
  • catnapwat's avatar
    catnapwat
    6 years ago

    Solved using TreatAs()

     

    GI from Forecast = CALCULATE(
SUM('GI from Forecast 2020 sheet'[Value]),
TREATAS(
SUMMARIZE(DateTable,DateTable[Year],DateTable[MonthOfYear]),
'GI from Forecast 2020 sheet'[Year],
'GI from Forecast 2020 sheet'[MonthNumber]
)
)


    Referencing this guide from SQLBI.


    I still have the bridge table for the client name, but the Date bridge didn't work as it created too many filter paths for my setup. However, passing the filter context to the secondary table using Treatas worked... a treat.

    Thanks for the assist guys.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    catnapwat - Your slicer should be based on your bridge table, right?

  • catnapwat's avatar
    catnapwat
    Icon for Helper I rankHelper I
    6 years ago

    Greg_Deckler  my slicer is based on my central Date table. Are you saying I need two bridge tables- Brand and Date?

  • catnapwat's avatar
    catnapwat
    Icon for Helper I rankHelper I
    6 years ago

    Solved using TreatAs()

     

    GI from Forecast = CALCULATE(
SUM('GI from Forecast 2020 sheet'[Value]),
TREATAS(
SUMMARIZE(DateTable,DateTable[Year],DateTable[MonthOfYear]),
'GI from Forecast 2020 sheet'[Year],
'GI from Forecast 2020 sheet'[MonthNumber]
)
)


    Referencing this guide from SQLBI.


    I still have the bridge table for the client name, but the Date bridge didn't work as it created too many filter paths for my setup. However, passing the filter context to the secondary table using Treatas worked... a treat.

    Thanks for the assist guys.