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...
Need Help
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