cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nallabothula Frequent Visitor
Frequent Visitor

How to keep a filtered measure value constant when we apply the same filter in a slicer?

Hello All,

 

I have created a measure for a dashboard as below :

 

Avg=
VAR F1=CALCULATE(COUNT('Data'[ID]),FIRSTDATE('Data'[CreatedDate]),'Data'[Action] <> "T")
VAR L1=CALCULATE(COUNT('Data'[ID]),LASTDATE('Data'[CreatedDate]),'Data'[Action] <> "T")
RETURN (F1+L1)/2
 
I am using the above measure to calculate the percentage KPI's and I have Data[Action] as a slicer on dashboard and when i select anything from the filter including 'T' , my requirement is that the 'Avg' measure should not change.
 
Example: My meaure is Req = count(Data[ID])/Avg. 
When I filter the data with Data[Action] ='T', the numerator should change but not the denominator. Denominator has to be same for all the values of Data[Action]. Is this possible? 
 
I have tried ALL and REMOVEFILTER functions but no luck.
 
Could someone please help me with the above issue. Thanks in advance.
5 REPLIES 5
lmf232s Member
Member

Re: How to keep a filtered measure value constant when we apply the same filter in a slicer?

@nallabothula , 

 

I may be misunderstaing what your saying but could you edit the interactions with your filter and the KPI visual? 

Take a look at this visual. I've edited the interactions so that when a value is selected in the filter the only value on the page that gets updated is the 'Column 3' card. You'll see that my table and the 'Column 2' card are no longer being filtered by the 'Column 1' filter. If this is viable solution you'll need to select your filter -> Click the edit interactions button -> and for each visual that you don't want filtered by that filter visual, you'll need to click the icon I have highlighed. 

 

2019-10-04_15-29-58.png

nallabothula Frequent Visitor
Frequent Visitor

Re: How to keep a filtered measure value constant when we apply the same filter in a slicer?

Thank you for the quick response.

 

In the above scenario, I am not showing the 'Avg' value in a KPI directly but i am using it to calculate other measures as a denominator value.

Output = calculate(count(Data[ID]))/Avg

 

The 'Avg' value should be constant for all the selected values of the Data[Action] Slicer.

 

lmf232s Member
Member

Re: How to keep a filtered measure value constant when we apply the same filter in a slicer?

Do you have other filters on the page? 

 

Also, what are you trying to accomplish with the FirstDate and LastDate in your Avg function? Those are being treated as a filter but what are you filtering? I put a simple dataset together with 3 columns (ID, CreatedDate, Action) and that avg calculation stays the same regardless of which action I select. 


Can you provide a sample dataset? 

Highlighted
nallabothula Frequent Visitor
Frequent Visitor

Re: How to keep a filtered measure value constant when we apply the same filter in a slicer?

Thanks for the Response.

Here is my sample data set  and 
Avg calculation= (clculate(count(num),firstdate(Startdate)

+ Calculate(count(num),lastdate(statdate))/2

Start Date NumActionPosition NumberDivision Code
7/1/20171P123
7/2/20171p225
7/3/20172TA345
7/4/20172TA443
7/5/20173T525
7/6/20175T626
     
gckcmc Member
Member

Re: How to keep a filtered measure value constant when we apply the same filter in a slicer?

you said you tried "ALL" which should help you here.  Here's a thread which may help on some syntax:

 

https://community.powerbi.com/t5/Desktop/Make-measure-ignore-specific-filter/td-p/336870

 

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors