cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ArjanL Frequent Visitor
Frequent Visitor

Three different filtered visuals based on one slicer

Hello,

 

I am new to Power BI and struggling to achieve the following.

 

I have contracts with a starting date and a termination date. 

SO:

ContractA  2015-10-05  2016-07-17

ContractB  2016-04-30  2017-11-03

ContractC  2014-01-01  2018-07-17

 

 

Now I'd like to have one slicer to select a year. And with that one year I want to display three visuals that contain the following data:

 

Say, I select 2016 in the slicer

I want one visual to show all contracts that were active in 2016: Start date <2016-01-01  End date > 2016-12-31

=> Contract C

One with all contract started in 2016: 2016-01-01 < Start date >2016-12-31

=> Contract B

One with all contract terminated in 2016: 2016-01-01 < End date >2016-12-31

=> Contract A

 

How can I split the contracts between the three visuals?

So far I was unable to figure out how to filter the visuals in different ways based on input by a slicer.

The table does contain more data which is used to make the visual. I just limited it now to the data needed to show my filtering problem.

 

4 REPLIES 4
v-huizhn-msft Super Contributor
Super Contributor

Re: Three different filtered visuals based on one slicer

Hi @ArjanL.

Please review the following steps and get expected result.

1. Create a separate table including 'Year' column, and create a slicer based on Year column.

2. Create three measure according to your logical thinking.

active in 2016 = CALCULATE(FIRSTNONBLANK(Test[Contract],1),FILTER(Test,AND(Test[starting date]<DATE(SELECTEDVALUE('Year'[Year]),1,1),Test[termination date]>DATE(SELECTEDVALUE('Year'[Year]),12,31))))

started in 2016 = CALCULATE(FIRSTNONBLANK(Test[Contract],1),FILTER(Test,AND(Test[starting date]>DATE(SELECTEDVALUE('Year'[Year]),1,1),Test[starting date]<DATE(SELECTEDVALUE('Year'[Year]),12,31))))

end in 2016 = CALCULATE(FIRSTNONBLANK(Test[Contract],1),FILTER(Test,AND(Test[termination date]>DATE(SELECTEDVALUE('Year'[Year]),1,1),Test[termination date]<DATE(SELECTEDVALUE('Year'[Year]),12,31))))


3. Create three visuals to display the result, please see it in the following screenshot.

1.GIF

 

Please download the attachment file for more details.

Best Regards,

Angelia

ArjanL Frequent Visitor
Frequent Visitor

Re: Three different filtered visuals based on one slicer

This is close to what I need, but I bump into another problem.

This function allways returns the first contract that meets the requirements.Say both ContractC and ContractD are active in 2016, I only get ContractC as a return.

 

For testing I made a calculated column with the formula and a fixed year.

And with an AND statement I added Contract[No_]=EARLIER(Contract[No_])

Then I get the Contract No of the current line as return value, what is what I want. 

 

So, I tried this in the measure, but then I get the error that EARLIER points to a row context that does not exist.

Is there another way to make sure I only get the current row as a return?

 

v-huizhn-msft Super Contributor
Super Contributor

Re: Three different filtered visuals based on one slicer

Hi @ArjanL,

Do you mind share your .pbix file for further analysis?

Thanks,
Angelia

Highlighted
praiselyabraham Regular Visitor
Regular Visitor

Re: Three different filtered visuals based on one slicer

Hi , 

 

Create a date table have relationship between your table and the date table using start date.

Create another inactive relation between date table to your table using the end date.

 

Create 3 measures, each for the 3 scenario's

Measure1 = VAR X=CALCULATE(count('Table1'[Id]),FILTER(
'Table1',
('Table1'[Start_Date]>= FIRSTDATE('Date'[Date]) &&
'Table1'[End_date] <= LASTDATE('Date'[Date]))))
RETURN IF(ISBLANK(X),0,X)

 

Measure 2= count([Id])

 

Measure 3= calculate( count([ID]),userelationship(table1[end_date],date[date]))

 

Now create 3 visuals, using measure 1 for the criteria 1, measure 2 for the criteria 2, measure 3 for the criteria 3.

 

Hope this solves your concern.

 

Regards.