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			16		B
3			12		B
3			13		B
3			13		A
3			14		B
3			15		A
3			15		B

 

and the values I present in the matrix is a result of the following measure:

 

Measure = 
VAR A = 
CALCULATE(
    COUNTROWS(VALUES(Data[Service])),
    ALLSELECTED(Data[Service])
)
RETURN
IF(
    A > 1,
    SUM(Data[Value]),
    BLANK()
)

 

essentially giving me this:

So what it's doing is ONLY showing results for PersonID and Service, where both services (chosen from a simple slicer) are present during the same week number.

 

The matrix works great, no problems there at all.

 

But the client now wants to know, how many distinct PersonID there are in the matrix. I've tried a simple DISTINCTCOUNT(PersonID) but that's of course giving me the total of either value from the slicer. I then tried to use the same logic as in the above measure:

 

Measure = 
VAR A = 
CALCULATE(
    COUNTROWS(VALUES(Data[Service])),
    ALLSELECTED(Data[Service])
)
RETURN
IF(
    A > 1,
    DISTINCTCOUNT(Data[PersonID])/A,
    BLANK()
)

 

but this works per line - a total of the results gives me an incorrect total (A manual count for example shows 30, but the result total in the card is 32...).

 

I hope this is clear enough - basically I need to take the results in the matrix (that works), and make a card displaying the number of PersonID that are displayed in the matrix.

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

10 Replies

  • 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

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

      • MFelix's avatar
        MFelix
        Super User

        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?

         

         

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee

    How about this expression?

     

    Person Count = CALCULATE(DISTINCTCOUNT(Date[PersonID]), ALLSELECTED(Data))

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

    • grggmrtn's avatar
      grggmrtn
      Post Patron

      Sorry, that's giving me the same result as just a simple DISTINCTCOUNT(Data[PersonID])