Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nunnc01
Helper I
Helper I

Simple measure not working

Hi,

 

Really confused over what to me should be a very simple calculation.

 

In my dataset I have three columns, Person ID, Date and Outcome. I have built a measure that counts all records - 

count('mytable'[Person ID]). Displaying this as a card I can see that this figure updates when I click on any visual so I can see how many for a certain month, certain outcome etc. That's great.
 
I also want a card visual to show how many records where the outcome = "Futher Enquiries" so have a measure that
appears to work - calculate(count('mytable'[Person ID]),'mytable'[Outcome]="Further Enquiries")).
 
This measure also updates when I click on a certain month BUT it does not update when I click on any visual that is using Outcome. If I click on any other outcome other than "Further Enquiries" I would expect this measure to show as 0 but it doesn't - it shows as the total number of records in the dataset where Futher Enquiries has been selected.
 
Can anyone offer any advice? I just can't understand why the above isn't working.
 
Thanks
 
1 ACCEPTED SOLUTION
Gordonlilj
Solution Sage
Solution Sage

Hi,

 

Try this

calculate(count('mytable'[Person ID]),FILTER('mytable','mytable'[Outcome]="Further Enquiries")) +0

View solution in original post

2 REPLIES 2
d_gosbell
Super User
Super User

This is how the sort of simple filter you have defined is supposed to work. This would effectively allow you to create ratio measures that compare "Further Enquiries" to other outcome types. However it is possible to make this work the way you want.

 

Effectively your measure:

 

calculate(count('mytable'[Person ID]),'mytable'[Outcome]="Further Enquiries")

 

Is short hand for the following:

 

calculate(count('mytable'[Person ID]),FILTER( ALL( 'mytable'[Outcome]), 'mytable'[Outcome]="Further Enquiries" ))

 

You can get the behaviour you require by using an expression like the following

 

calculate(count('mytable'[Person ID]),FILTER(VALUES('mytable'[Outcome]), 'mytable'[Outcome]="Further Enquiries" ))

 

Using VALUES() only returns outcomes in the current filter context. So if you pick anything other than "Further Enquiries" it will return 0.

Gordonlilj
Solution Sage
Solution Sage

Hi,

 

Try this

calculate(count('mytable'[Person ID]),FILTER('mytable','mytable'[Outcome]="Further Enquiries")) +0

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.