Dynamic filtering of columns

[Anonymous]

Hi All

I have a requirement as below:

A table the no. of requistions:

There is a date slicer, based on the date selected, only the Open and Closed columns should filter, The Total column should remain the same.

Eg: If 7 days is selected, the Total should remain as is,( the total values from the beginning), Open Column should show the open positions in the last 7 days, Closed should show the closed positions in the last 7 days

Can someone please let me know how to achieve this?

Thanks

Poojitha

[Greg_Deckler]

@Anonymous - It is difficult to say with the information provided but it probably involves the use of the ALL function in your calculation for Total. However, since you did not post the formula it is difficult to say for certain.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi,

Since we did not get a solution, I am unable to put any screenshots from Power BI, The data below is from Excel to reproduce the solution

The first table below shows the total no of requsitons , open requistions, closed requistions for a recruiter.

When a user selects the date from the range slicer, eg, if he selects from 5/20/2020 to 5/29/2020, then the output should look like the second column, which is the Total should still show Total requistions(same as table 1), the Open should show the open requistions between 5/20/2020 to 5/29/2020 and Closed should show closed requistions between 5/20/2020 to 5/29/2020.

The second table says that for recruiter A, the total is 10 which is same as table A, the open requistions opened between 5/20/2020 to 5/29/2020 are 1 and closed are 1,

For B, the total is 20 same as table 1, there were no requisitions which were opened or closed between 5/20/2020 to 5/29/2020.

Expectation is to show the second table as output in power bi.

[v-xicai · Accepted solution]

Hi @Anonymous  ,

You may create measures like DAX below.

Open Count=

Var _StartDate = CALCULATE(MIN ('range slicer'[Date]), ALLSELECTED('range slicer'[Date]))

Var _EndDate = CALCULATE(MAX ('range slicer'[Date]), ALLSELECTED('range slicer'[Date]))

Return

CALCULATE(COUNT(Table1[open requistions]),FILTER(ALLEXCEPT(Table1, Table1[Recruiter]),  Table1[Date]>=_StartDate && Table1[Date]<=_EndDate ))



Close Count=

Var _StartDate = CALCULATE(MIN ('range slicer'[Date]), ALLSELECTED('range slicer'[Date]))

Var _EndDate = CALCULATE(MAX ('range slicer'[Date]), ALLSELECTED('range slicer'[Date]))

Return

CALCULATE(COUNT(Table1[closed requistions]),FILTER(ALLEXCEPT(Table1, Table1[Recruiter]),  Table1[Date]>=_StartDate && Table1[Date]<=_EndDate ))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.