Forum Discussion

ArjanL's avatar
ArjanL
Frequent Visitor
8 years ago

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.

 

Need Help

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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.

     

    Please download the attachment file for more details.

    Best Regards,

    Angelia

    • ArjanL's avatar
      ArjanL
      Frequent Visitor
      8 years ago

      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?

       

  • praiselyabraham's avatar
    praiselyabraham
    Icon for Resolver II rankResolver II
    8 years ago

    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.