Forum Discussion

Post Patron

6 years ago

Solved

Counting filtered data (AND filter)

I've created a matrix visual based on this sample data: PersonID WeekNr Service 1 12 A 1 12 B 1 13 A 1 13 B 1 14 A 1 15 A 2 12 A 2 13 B 2 15 A 2 15 B 2 16 A 2 ...

6 years ago

this has to do with contex of the measure try the following:

Measure 2 = 
var temp_table = SUMMARIZE(Data; Data[PersonID];Data[WeekNr]; "@Value";[Measure])
return
CALCULATE (
    DISTINCTCOUNT ( Data[PersonID] );
    FILTER(temp_table; [@Value] > 0)
)

Replace the Data[WeekNr] by the date column, should work as expected.

Super User

6 years ago

Try the following measure:

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( Data[PersonID] ),
    FILTER ( Data, [Measure] <> BLANK () )
)

The [Measure] refered in this one is the first one you use for your matrix.

Post Patron

6 years ago

hi MFelix - thanks for the reply... the resulting card for this just shows '(Empty)' though 😞

MFelix
Super User
6 years ago
Hi grggmrtn ,

On the test I have made the result in the card is correct, as you can see below when I filter out the week the number of persons is changing accordingly. This model may not match with yours I used your sample data but instead of summing value i summed the week but the result is similar.

Are you abble to share a mockup file and expected result?
- grggmrtn
  Post Patron
  6 years ago
  Hi again MFelix - unfortunatly I'm not able to share a mockup file since it's all GDPR protected, and creating a dummy would take way too long.
  I can see that it SHOULD be working - I just can't figure out why I'm getting an Empty result. My data matches the test data, I don't have any other filters that are getting in the way... frustrating 😞
  - MFelix
    Super User
    6 years ago
    Is your data only with the table you have show, or are there any other tables?
    
    Be aware that measures are calculated based on context, meaning that any filters, slicers, visualizations settnigs, measure setup can influence the final result.
    
    Do you have any other filtering applied on the report?
    
    You refer that you have a slicer for the service person correct? how is that setup? maybe it's influencing the result of the second measure.

Related content