Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
ACSchnitzers
Frequent Visitor

Multiple filter selection on one table To Filter Main Table, But Main Table Shows no results

Summary of Ask: Need a filter that will allow for multiple selections in the cooresponding slicers. Currently, this filter only works for a single selection.

 

Filter Measures are below and photos of the current result. 

 

Thank you in advance.

 

Details:

 

I have 2 tables:

  • UpdatedQuery: Main Data Table. The related columns: EntityName, ComNoModelNo, Dates,
  • MaxCountsStartsbyDateDiv: Subset of the Main Table just showing EntityName, ComNoModelNo, Dates, Counts (How many times each ComNoModelNo appear on different dates), and MaxCount for each date

I have managed to create a measure that counts the Max of MaxCount between 2 Dates for Each EntityName. I use this measure as a visual filter "Is Not Blank". This one works. I select an Entity in the "Max" Slicer and the table filters to show the Max MaxCount between 2 dates selected then Filters to show the minimum date in that group to avoid additional ComNoModelNo's with the same MaxCount.

The Filter I am successful with is below- TryMax

 

What I am attempting to do is use the outcome of the filter on MaxCountsStartsByDateDiv and filter the UpdatedQuery table to only show the ComNoModelNo's from MaxCountsStartsByDateDiv.

 

I can get the filter below- TryMaxUpdate to show one ComNoModelNo, but if I select multiple EntityNames, the table is blank. 

 

This Filter Works on the MaxCountsStartsbyDateDiv table when I select an EntityName on a slicer. 

TryMax = 
VAR _Entity = SELECTEDVALUE(MaxCountsStartsbyDateDiv[EntityNo])
VAR _Div = SELECTEDVALUE(MaxCountsStartsbyDateDiv[EntityName])

VAR _MinDate = Min(DateTable[Date])
VAR _MaxDate = Max(DateTable[Date])
VAR _MaxCount = Calculate(Max(MaxCountsStartsbyDateDiv[MaxDivStartCountByDate]),AllExcept(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo]),MaxCountsStartsbyDateDiv[PrevDate] >= _MinDate && MaxCountsStartsbyDateDiv[PrevDate] <= _MaxDate, MaxCountsStartsbyDateDiv[EntityNo] = _Entity)
VAR _EarliestDate = CALCULATE(Min(MaxCountsStartsbyDateDiv[PrevDate]),ALLEXCEPT(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo],MaxCountsStartsbyDateDiv[ComNoModelNo]),MaxCountsStartsbyDateDiv[MaxDivStartCountByDate] = _MaxCount, MaxCountsStartsbyDateDiv[EntityNo] = _Entity)
VAR _ComMod = Calculate(min(MaxCountsStartsbyDateDiv[ComNoModelNo]),ALLEXCEPT(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo]),MaxCountsStartsbyDateDiv[PrevDate] = _EarliestDate,MaxCountsStartsbyDateDiv[MaxDivStartCountByDate] = _MaxCount,MaxCountsStartsbyDateDiv[EntityNo] = _Entity,MaxCountsStartsbyDateDiv[EntityName] = _Div)
VAR _DivName = Calculate(min(MaxCountsStartsbyDateDiv[EntityName]),ALLEXCEPT(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo]),MaxCountsStartsbyDateDiv[PrevDate] = _EarliestDate,MaxCountsStartsbyDateDiv[MaxDivStartCountByDate] = _MaxCount,MaxCountsStartsbyDateDiv[EntityNo] = _Entity,MaxCountsStartsbyDateDiv[EntityName] = _Div)
RETURN

IF(ISFILTERED(MaxCountsStartsbyDateDiv),COUNTROWS(FILTER(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityName] = _DivName && MaxCountsStartsbyDateDiv[PrevDate] = _EarliestDate && MaxCountsStartsbyDateDiv[ComNoModelNo] = _ComMod)),CountRows(MaxCountsStartsbyDateDiv))

 

This Filter only allows for one EntityName to be filtered at a time:

TryMaxUpdate = 
VAR _Entity = SELECTEDVALUE(MaxCountsStartsbyDateDiv[EntityNo])
VAR _Div = SELECTEDVALUE(MaxCountsStartsbyDateDiv[EntityName])

VAR _MinDate = Min(DateTable[Date])
VAR _MaxDate = Max(DateTable[Date])
VAR _MaxCount = Calculate(Max(MaxCountsStartsbyDateDiv[MaxDivStartCountByDate]),AllExcept(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo]),MaxCountsStartsbyDateDiv[PrevDate] >= _MinDate && MaxCountsStartsbyDateDiv[PrevDate] <= _MaxDate, MaxCountsStartsbyDateDiv[EntityNo] = _Entity)
VAR _EarliestDate = CALCULATE(Min(MaxCountsStartsbyDateDiv[PrevDate]),ALLEXCEPT(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo],MaxCountsStartsbyDateDiv[ComNoModelNo]),MaxCountsStartsbyDateDiv[MaxDivStartCountByDate] = _MaxCount, MaxCountsStartsbyDateDiv[EntityNo] = _Entity)
VAR _ComMod = Calculate(min(MaxCountsStartsbyDateDiv[ComNoModelNo]),ALLEXCEPT(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo]),MaxCountsStartsbyDateDiv[PrevDate] = _EarliestDate,MaxCountsStartsbyDateDiv[MaxDivStartCountByDate] = _MaxCount,MaxCountsStartsbyDateDiv[EntityNo] = _Entity,MaxCountsStartsbyDateDiv[EntityName] = _Div)
VAR _DivName = Calculate(min(MaxCountsStartsbyDateDiv[EntityName]),ALLEXCEPT(MaxCountsStartsbyDateDiv,MaxCountsStartsbyDateDiv[EntityNo]),MaxCountsStartsbyDateDiv[PrevDate] = _EarliestDate,MaxCountsStartsbyDateDiv[MaxDivStartCountByDate] = _MaxCount,MaxCountsStartsbyDateDiv[EntityNo] = _Entity,MaxCountsStartsbyDateDiv[EntityName] = _Div)

RETURN

IF(ISFILTERED(MaxCountsStartsbyDateDiv),COUNTROWS(FILTER(UpdatedQuery,UpdatedQuery[EntityName] = _DivName && UpdatedQuery[PrevDate] = _EarliestDate && UpdatedQuery[ComNoModNo] = _ComMod )),Countrows(UpdatedQuery))

 

I would like to have this filter allow for multiple EntityNames.

 

Left Table is Max...Table, Slicer is based on this table's EntityName. Right Table is All data.Left Table is Max...Table, Slicer is based on this table's EntityName. Right Table is All data.Selecting 1 EntityName results as expectedSelecting 1 EntityName results as expected2 or More EntityNames result as expected on the Max Table, but yields no records on the main table.2 or More EntityNames result as expected on the Max Table, but yields no records on the main table.

0 REPLIES 0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.