cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Two Date Slicers and Filter in DAX measure

I have created a general setup similar to what's described here https://www.sqlbi.com/articles/filtering-and-comparing-different-time-periods-with-power-bi/#code1 so I have two independent date slicers ("Date.1" and "Date.2") on one page. 

 

Then I created few measures adding "ALL('Date.1'[Date]),USERELATIONSHIP('Date.1'[Date],'Date.2'[Date])" within my Calculate function. Most of the time it works perfectly but I have a problem whenever I need to use an other Filter function (see example below) in my measure. It looks like if my selected range for "Date.2" is not within Date.1 result is wrong. Either the starting date or ending date of Period-1 will still affect my measure for Period-2 which is obviously not what I want.

 

Here is an example:

- This formula counts the number of different production line being operated (meaning with production >0) over a specific period:

DIFF LINE PREV. = CALCULATE(DISTINCTCOUNT(Database[LINE #]),FILTER(Database,[DAILY PLAN Prev. P.]>0),ALL('Date.1'[Date]),USERELATIONSHIP('Date.1'[Date],'Date.2'[Date]))

- The underlying formula for production (Daily Plan Prev. P.) is:

DAILY PLAN Prev. P. = CALCULATE(SUM(Database[DAILY PLAN]),ALL('Date.1'),USERELATIONSHIP('Date.1'[Date],'Date.2'[Date]))

If Date.2 is withing Date.1 it works fine but now if Date.1 is before Date.2 result is "(Blank)". I guess I am missing something in

"Filter(Database[Daily Plan Prev. P]>0)"  as if I delete this part of the formula Date.1 no longer affects my measure. However result is still wrong as it returns all lines even if the production is 0.

 

Does anyone know what I am missing here to completely segregate this measure from my Date.1 filter?

 

Thanks

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

Re: Two Date Slicers and Filter in DAX measure

Hi @Anonymous

 

You may change the relationship first and then create the measures with USERELATIONSHIP Function. Here is the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
v-cherch-msft Super Contributor
Super Contributor

Re: Two Date Slicers and Filter in DAX measure

Hi @Anonymous

 

Could you share some data sample and expected output? You can upload it to OneDrive or Dropbox and post the link here. 

How to Get Your Question Answered Quickly

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: Two Date Slicers and Filter in DAX measure

Hi,

 

I actually found a workaround by editing the interaction of the first Date filter so that it does not affect this KPI but it is not ideal. I would rather fix the formula itself. Will share shortly data + output.

 

Thanks.

Anonymous
Not applicable

Re: Two Date Slicers and Filter in DAX measure

HI @v-cherch-msft

 

Here is an example: Dropbox

 

Thanks,

v-cherch-msft Super Contributor
Super Contributor

Re: Two Date Slicers and Filter in DAX measure

Hi @Anonymous

 

You may change the relationship first and then create the measures with USERELATIONSHIP Function. Here is the sample file for your reference.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Anonymous
Not applicable

Re: Two Date Slicers and Filter in DAX measure

Thanks @v-cherch-msft works well but I also need to update my formula on the left hand size to be filtered with Date Slicer 1:

DIFF LINE = CALCULATE(DISTINCTCOUNT(Data[Line #]),FILTER(Data,[Prod.]>0),USERELATIONSHIP(Data[Date],'Date.1'[Date]))

 

So I would need to create new measures for both my Date Slicer-1 and 2 which is not ideal but I still acceptable.

 

 

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 162 members 1,564 guests
Please welcome our newest community members: