Subcategory Based on filtered values - Matrix Table

Ensure Relationships Are Set

• Your Forecast/Actuals table should be linked to a Date table via a date field (e.g., Month).
• Your slicer should be based on the Date table (e.g., DateTable[Month]).

2. Create a Classification Column

In Power BI, create a calculated column like this:

ProductStatus = VAR SelectedMonth = MAX('DateTable'[Month]) VAR ForecastValue = LOOKUPVALUE(FactTable[Forecast], FactTable[Month], SelectedMonth, FactTable[Product], FactTable[Product]) VAR ActualValue = LOOKUPVALUE(FactTable[Actuals], FactTable[Month], SelectedMonth, FactTable[Product], FactTable[Product]) RETURN SWITCH( TRUE(), ISBLANK(ForecastValue) && NOT(ISBLANK(ActualValue)), "No Forecast w Actuals", NOT(ISBLANK(ForecastValue)), "Forecasted", "Other" )

Replace FactTable with your actual table name.

This column will classify each product based on the selected month.

3. Use the Classification in a Slicer

• Add ProductStatus to a slicer.
• Now users can filter by “No Forecast w Actuals” or “Forecasted”.

4. Matrix Visual Setup

• Rows: Product
• Columns: Month (from Date table)
• Values: Forecast, Actuals
• Apply slicers for Month and ProductStatus

Hi Martz86,
Thank you for the Follow-up Question.

You have understood it correctly. The approach uses two helper measures (Has Actuals and Has Forecast) along with a disconnected table that acts as the slicer. Let me break it down step by step so you can replicate it in your own report.

Create helper measures that simply check if a product has values for Actuals or Forecast both are different measures: 

Has Actuals =
IF ( NOT ISBLANK ( SUM ( Data[Actuals] ) ), 1, 0 )
Has Forecast =
IF ( NOT ISBLANK ( SUM ( Data[Forecast] ) ), 1, 0 )

Create a disconnected slicer table that gives users a choice:

SlicerTable =
DATATABLE ( "Option", STRING, { {"Forecasted"}, {"No Forecast w Actuals"} } )

Add this table to your model without creating relationships.

Create a filter measure that applies the slicer selection:

Show Filtered Data =
SWITCH (
SELECTEDVALUE ( SlicerTable[Option] ),
"Forecasted", [Has Forecast],
"No Forecast w Actuals", [Has Actuals]
1. )

Apply the filter measure by placing Show Filtered Data in the Visual-level filter of your table/matrix and setting it to “is 1”.
This way, when a user toggles the slicer, the visual will only show rows that match the chosen condition. 

If you would like, feel free to share a small sample dataset with your expected outcome I can then help tailor the DAX more closely to your exact model.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.