Slicing with Start and End date

[Anonymous]

Hello to all

This is probably a easy one, but i'm not managing to make it work.

I have a table with this

ID StartDate EndDate

1  1/1/2018 14/1/2018

2  15/1/2018 30/1/2018

and another like:

ID Date Value

1  1/1/2018 1

2  2/1/2018 2

3  3/1/2018 3

4  15/1/2108 4

5  16/1/2018 5

What I would like to do is to a slicer using the dates on the first table (slicer using ID) to get the values of the second table.

In this case

ID 1 would give values 1, 2 and 3

ID 2 would give values 4 and 5

I tried to link StartDate from table 1 to Date of table 2... but only get the values of startdate..

Any ideias?

Regards

[TomMartens]

Hey,

don't link the tables 🙂 sounds odd but it will work ...

Create a Measure "is between" in your second table - I call this table "Fact" and the 1st one "Slicer":

is between = 
IF(HASONEVALUE('Slicer'[ID])
    ,var vStartDate = FIRSTNONBLANK('Slicer'[StartDate],1)
    var vEndDate = FIRSTNONBLANK('Slicer'[EndDate],1)
    return
    SUMX('Fact'
        ,IF(AND('Fact'[Date] >= vStartDate,'Fact'[Date] <= vEndDate),1,BLANK())
    )
)

Now you can use this measure on any visual to filter if the measure "is not blank"

In my example I'm using a table with the content from "Slicer" to filter the content of the 2nd table visual.

Hopefully this is what you are looking for.

Regards

Tom

[Anonymous]

Hello @TomMartens

Thanks for the answer, we are one step closer. 🙂 Altough I'm not sure if it is working.. 

The Fact table (as you called it) seems to be filter as expected but I'm getting

is between with values like 16,13,15.. which might be correct because I have several entries with the same day (it's a log with several entries).

But since I use a matrix to display the data I want it seems the matrix is not filter using the visual filter you used.

What can I do to change that?

Do you need more on my model?

Thanks

[TomMartens]

Hey @Anonymous,

sounds good, a step closer is always better than a step away 🙂

I can't reproduce what you say regarding the Matrix Visual, as you can see in the screenshot down below, also the matrix visual gets filtered:

Did you use the measure "in between" on Visual level filters Panel of the Matrix Visual.

If you did than it's most lilkely that I do not fully understand your requirement, maybe you can create sample data, upload the pbix file to onedrive or dropbox and share the link.

Regards

Tom

[Anonymous]

@TomMartens

Can you give me a more complete image of your matrix?

In fact my matrix fetchs a description from a 3rd table...  that is linked to the Fact table. Maybe this is the problem because powerbi can not filter since it has no relationship.

I will try to get a small example to show you.

Regards and thanks

[TomMartens]

Hey,

wondering what you mean by a more complete image. Due to filtering the complete Matrix is shown.

A sample PBIX (reduced to the max) would be really helpful 🙂

Regards

Tom

[Anonymous]

Hello again @TomMartens

Tried again but not working. In the image we can see that altough I have the Visual Filtered by "is between is not blank" It is displaying blanks.. 😞

If I use a table it works but is do no add the days worked by summary.

Any ideias? 😞

Thanks

[Anonymous]

Hello @TomMartens

While doing the redux version.. a strange thing occured.. it works.

I have to see what is filtering (or not) in the full model.

I will keep you posted. 🙂

Thanks