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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors