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

Highlighted
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.

 

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
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors