Conditional Filtering

[mgeorge0131]

Hello fellow Power BI Users!

I have a report with various cards, tables, and graphs, and slicers for spending groups and quarter. The particular piece of data I have an issue with is the summarization of headcount I am using. The data is formated like the table below. When the plage slicer is filtered on a particular quarter, everything is fine. However when I am not filtered on any quarter (ie to look at full year spending), the headcount card sums each quarter. For example, if headcount at the end of each quarter was 10, it would show 40, when ideally it would just show the most recent quarters headcount.

Is there a way that I can have the headcount card filter when a quarter is selected, but when no quarter is selected just show the most current piece of data (ie if in July looking at the prior 4 quarters of data only show Q2 headcount)? I'm thinking there is some DAX formula I can use, but can't seem to get it.

Group	Quarter	Headcount
Group 1	Q1	54
Group 1	Q2	49
Group 1	Q3	17
Group 1	Q4	39
Group 1	Q1	32
Group 1	Q2	52
Group 1	Q3	19
Group 1	Q4	43
Group 2	Q1	36
Group 2	Q2	59
Group 2	Q3	51
Group 2	Q4	26
Group 3	Q1	29
Group 3	Q2	19
Group 3	Q3	32
Group 3	Q4	41

Thanks for your help!

-

Mike

[karthik]

We call these as semi-additive measures (in your case it's last non empty or last non blank over time) and there are existing functions in DAX like LASTNONBLANK

Have a look at this blog - http://www.sqlbi.com/articles/semi-additive-measures-in-dax/

[Greg_Deckler]

I'm thinking that the DAX formula that you want is ISFILTERED.

https://msdn.microsoft.com/en-us/library/gg492163.aspx

Basically, create a measure that goes something like this:

IF(ISFILTERED('table'),"calculate for quarter selected","calculate a different way for year using something like MAX based upon a date or something")

I'll input your data into a test environment and see if I can be more specific.

EDIT: @mgeorge0131 - One other thought on this, can you provide the formula for your measure today? From the data, I am not sure that I understand what you mean that "filtered by particular quarter everything is OK". From the data, I see 2 Group 1 Q1, Q2, Q3, Q4 values. I am thinking that if you filtered to Group 1 and any of the quarters that you are going to get the sum of the quarters, which I am thinking you don't want but could be wrong.

[mgeorge0131]

Thanks @Greg_Deckler

Currently there is not a formula for the measue. The data is numbers, so it is summed automatically. No special formula. What i mean by "filtered by particular quarter" is when i use a page slicer and select Q1, for instance, the data appears correctly, but if i select say Q1 and Q2 to look at half a years data it sums Q1 and Q2 headcount instead of just showing Q2 (ie the most recent) data.

Are you able to be a little more specific on your formula so i can try it? I'm not too sure how to calculate the last part of your formula.