Forum Discussion
Three different filtered visuals based on one slicer
Microsoft EmployeeHi 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
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
Hi ArjanL,
Do you mind share your .pbix file for further analysis?
Thanks,
Angelia