Filter slicer by Date range

[szabkel]

Model:

DAX Table:

DocDateRowSlicer = CALENDAR(DATE(2012,1,1),TODAY())

Reason:

The DocDate slicer is connected to the row tables, because even if your document is dated at an X date, you can add rows later in some cases. So it must include all documents that has row DocDates in the selected range.

Goal:

First, the user selects the Business Partner (single) and sets the date range. I have two slicers, one where the BusinessPartner is selectable and one where the DocDateRowSlicer is selectable (using a range).

Problem:

I have multiple pages and sync slicers enabled for the two slicers. On a different page than the slicers, I have two additional slicers for SalesOrder and SalesOrderRow, allowing multiple selects.

Even though the the SO and SOR slicer displays as being affected by the date range, they only gets filtered by the "DisplayText" field (which is from the BusinessPartner table).

My guess: The BP selection gets through the BP -> SalesOrder -> SalesOrderRow path (both slicer gets filtered by the BP), but the DocDate slicer only gets to DocDateRowSlicer -> SalesOrderRow and not to SalesOrder (SalesOrder slicer is filtered only by the BP, not the Date, but the SalesOrderRow slicer is filtered by both).

Problem is, I can't connect DocDateRowSlicer[DocDate] to SalesOrder[DocDate]. First, because I can't, it would throw an error saying ambiguous paths, second, because I want to include rows with different DocDates.

Question:

For the SalesOrder slicer, how can I only include entries, which have at least a single row, also taking into account that the rows are filtered by the date slicer?

I tried to add a measre to SalesOrder "COUNTROWS(SalesOrderRows)" and add that to the slicer's Filter on this visual section (greater than 0), but it is not working. Still displays SalesOrders without restrictions on the DocDate.

 

Sample PBIX here.

[amitchandak]

@szabkel ,Based on what I got.

if you are using a date range, then I doubt a measure filter at the visual level will help. It can help to reduce a date list. In case of range it does not work

[szabkel]

"if you are using a date range, then I doubt a measure filter at the visual level will help. It can help to reduce a date list. In case of range it does not work "

If I can't pass the range filter's values back to DAX, how else can I use the user input to manually filter the rows? Do I need to model this a totally different way?

Even If I add measures to SalesOrder or SalesOrderRow, they always just work generally for all the values and I can't connect the two (SalesOrder measure -->  Slicer filter by measure  <--  DocDate range slicer)

[V-lianl-msft]

Hi @szabkel ,

This may be caused by the filtering direction. Please try to modify the filtering direction to ensure that DocDate range slicer can affect the context of SO.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[szabkel · Accepted solution]

I solved it like this:

Where the SalesOrderRowDateRanges table in DAX is:

SalesOrderRowDateRanges = CALCULATETABLE(DISTINCT(
    ALL(SalesOrderRow[DocEntry], SalesOrderRow[DocDate])
))

 This way, a Sales Order is displayed if it has at least one row selected by range.

The two way filter direction was needed as well, thought I read it everywhere I should avoid it at all cost.

[szabkel]

I am trying to only include SalesOrder entries (in my SalesOrder slicer) that have at least one row from the SalesOrderRow entries (which happens to be filtered by the DocDate range slicer).

Tried to add this to the SalesOrder table now (did not work):

SlicerHasRows = CALCULATE(
  COUNTROWS(SalesOrderRow),
  CROSSFILTER(SalesOrder[DocEntry], SalesOrderRow[DocEntry], BOTH)
)

[amitchandak]

@szabkel ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[szabkel]

I had to create a new file and manually enter some data. (Only had sensitive information...)

The second slicer (rows) gets filtered by the date range, but the first do not (should only display a row, if it had a SalesOrderRow).

@amitchandak :

Download the pbix file here.