Subcategory Based on filtered values - Matrix Table

[Martz86]

Hi,

I want to create a slicer for products without forecast for current month. Table is similar to below and I have a slicer that I use to select the month I'm looking at. Say I want data for just July (click the July slicer button). Goal would be to have a button or put in the field that is No Forecast w actuals or Forecasted. 

Prod	Forecast	Actuals
Product 1		100
Product 2	50	25

I've created a new column in data model but it does have relationship to my period slicer in using.

Thanks,
Dean

[anilgavhane · Accepted solution]

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

[anilgavhane · Accepted solution]

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

[v-kpoloju-msft]

Hi @Martz86,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @lbendlin, for his inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it.

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.

[Martz86]

Hi @v-kpoloju-msft 

This is great however can you please explain how this was accomplished? You created two measures "Has Actuals" & "Has Forecast". Then created a table to be used as a slicer which toggles between the conditions? Is this correct? I'm trying to understand the measures required and relationships needed to implement on my report.

Thanks,
Dean

[v-kpoloju-msft · Accepted solution]

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.

[Martz86]

Hi @v-kpoloju-msft ,

Thank you so much! This helps tremendously to simplfy the report! I was using bookmarks but that was a bit wonky and managing that many users was not feasible. This works amazing!!

Thanks,

Dean

[lbendlin]

Use the Filter Pane.  Teach your users how to exclude "(Blank)"  values in the "Forecast"  column.