Forum Discussion

JacobCoops's avatar
JacobCoops
Frequent Visitor
6 years ago
Solved

Count If measure

Hello

 

I am trying to create a measure that returns the percentage of customers with 3 or more logins. My dataset is a list of CustomerIDs and timestamps (logins) where one row equals one login. If i were to do this in Excel i would use the CountIf function on a pivoted dataset (to group the customer IDs):

 

 

 

 

 

 

 

 

 

 

 

My dataset look like this:

 

 

 

 

 

 

 

 

 

I have tried creating a calcuated coloumn that correctly counts the total number of logins pr. CustomerID (KundeID in the picture). The issue is, however, that the calculated coloumn does not respond to the date-slicers in the report. The users therefore cant choose to only evaluate 2020 data, for instance, which is why i figured it had to be a measure.

Based on the picture above the measure would return the number 2 since Customer ID 96090 is represented 5 times and ID 96045 is represented  4 times and the remaining two is shown 1 time each.

 

My current attempts have led me to this: 

 

 

 

 

 

 

 

 

 

 

 

 

 

What i would want the measure to do is to summarize the coloumn "Countrows 3".

 

As a bonus question i would like to know, if it is possible to change the threshold "3" to a dynamic value that users can change in a slicer?

 

I am a complete novice at PBI and DAX so there might be something obvious that i am missing so i appreciate any pointers. 

Thank you for your time

 

  • Measure = 

      VAR __Table = 

        FILTER(

          SUMMARIZE(

            'Table',

            [KundeID],

            "__Count", COUNTROWS('Table')

          ),

         [__Count] > 3

      )

    RETURN

      COUNTROWS(__Table)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Measure = 

      VAR __Table = 

        FILTER(

          SUMMARIZE(

            'Table',

            [KundeID],

            "__Count", COUNTROWS('Table')

          ),

         [__Count] > 3

      )

    RETURN

      COUNTROWS(__Table)

    • JacobCoops's avatar
      JacobCoops
      Frequent Visitor

      Hi Greg

       

      This is friggin amazing. Thank you very much!