Power bi multiple slicer

[Anonymous]

Hi,

I have 2 measures from one fact table, each measure is in a different matrix, and each measure is sliced by different date  (each date has a calender dimension).

I have two matrix and two calendars in my report.

Can I select a date from one calendar and automaticlly passed to the other calendar? 

The aim is to make the user use only one slicer.

Thanks

[V-pazhen-msft]

Hi , @Anonymous 

Could you provide more information maybe a sample data including relationships between tables.

Best, 

Paul

[Anonymous]

Hi Paul,

Thanks for your quick reply.

Below is a smaple of my report. Box 1 and 2 are in one page, the measures are 'Opening WIP' and 'Jobs Tasks', both are from one fact table, each measure is using differernt date in the fact table (Posting date and Production date), for each date there is seperate calendar dimension, in this report we use week of the year. 

What I want to have one slicer (calendar) in the report and can pass the selected week value to both boxes.

[Ashish_Mathur]

Hi,

There should not be a need to create 2 calendar tables.  There should be 2 relationships (from the Posting date and Production date columns) from the base data table to the Date column of the Calendar Table - one active and another inactive.  In the measure, one can then use the USERELATIONSHIP function to get the measures working.

[Anonymous]

Thanks Ashish.

One thing i didn't mentioned is that a tabular model is feeding the power bi reports, so I have tried to create the relation you have mentioned, but the tabular model did not let me and got below error:

[Anonymous]

sorry, ignore my previous reply. I have managed to create the relation  by unticking the active box.

I will update you soon.

[Anonymous]

Hi,

it did work.

I have tried below measures, none worked:

measure 1 =

VAR mindate =
MIN ( 'Date'[DateLookupKey] )
VAR maxdate =
MAX ( 'Date'[DateLookupKey] )
RETURN
CALCULATE (
COUNTROWS ( 'FactTable' ),
USERELATIONSHIP('Date'[DateLookupKey],'JobAnalysisViewEntry'[ProductionDateLocalLookupKey]),
FILTER (
'FactTable',
'FactTable'[Rework] = 0
&& 'FactTable'[Supplementary] = 0
&& 'FactTable'[ProductionDateLocalLookupKey] >= mindate
&& 'FactTable'[ProductionDateLocalLookupKey] <= maxdate

))

measure2=

VAR mindate =
MIN ( 'Date'[DateLookupKey] )
VAR maxdate =
MAX ( 'Date'[DateLookupKey] )
RETURN
CALCULATE (
COUNTROWS ( 'FactTable' ),
USERELATIONSHIP('Date'[DateLookupKey],'JobAnalysisViewEntry'[ProductionDateLocalLookupKey]),
FILTER (
'FactTable',
'FactTable'[Rework] = 0
&& 'FactTable'[Supplementary] = 0

))

not sure what am I doing worng.

[Anonymous]

Hi,

Sorry for the late reply.

Now I am using one calendar for the different measures, thanks for your help.

I have another measure that uses a third different date, I have created another inactive relationship for the new date, but it still uses the date of the active relationship. is this because we can have only one active and one inactive relationship between the two tables?