cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nallabothula
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 Resolver II
Resolver II

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

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.

 

Highlighted
lmf232s Resolver II
Resolver II

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? 

nallabothula
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 Resolver I
Resolver I

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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors