Forum Discussion

ACSchnitzers's avatar
ACSchnitzers
Frequent Visitor
3 years ago

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.Selecting 1 EntityName results as expected2 or More EntityNames result as expected on the Max Table, but yields no records on the main table.

No RepliesBe the first to reply