Forum Discussion

grggmrtn's avatar
grggmrtn
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 ...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi grggmrtn ,

     

    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.

MFelix's avatar
MFelix
Super User
6 years ago

Hi grggmrtn ,

 

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. 

  • grggmrtn's avatar
    grggmrtn
    Post Patron
    6 years ago

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

    • MFelix's avatar
      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's avatar
        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 😞