Forum Discussion

Ilse_ScpDt's avatar
Ilse_ScpDt
Icon for Helper I rankHelper I
4 years ago
Solved

Count of count

Hi,   I have a table with vacanies and applicants.   Vac Appl 1     A 1     B 1     C 2     D 3     E 3     F 4     G   I counted the number of Applicants per Vacany wth following formul...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating measures.

     

     

    Count measure: =
    VAR currentVAC =
    MAX ( Data[Vac] )
    VAR newtable =
    FILTER ( ALL ( Data ), Data[Vac] = currentVAC )
    RETURN
    IF ( HASONEVALUE ( Data[Vac] ), COUNTROWS ( newtable ) )
     
    Count2 measure: =
    VAR currentCount = [Count measure:]
    VAR newtable =
    FILTER (
    SUMMARIZE ( ALL ( data ), Data[Vac], Data[Count CC] ),
    Data[Count CC] = currentCount
    )
    RETURN
    COUNTROWS ( newtable )
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    If you want to add slicer, please try to use ALLSELECTED function, instead of using ALL function.

    Please check the attached file.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

Hi,

Please check the below picture and the attached pbix file.

It is for creating new columns.

 

 

Count CC =
VAR currentVAC = Data[Vac]
VAR newtable =
FILTER ( Data, Data[Vac] = currentVAC )
RETURN
COUNTROWS ( newtable )
 
Count2 CC =
VAR currentCount = Data[Count CC]
VAR newtable =
FILTER (
SUMMARIZE ( data, Data[Vac], Data[Count CC] ),
Data[Count CC] = currentCount
)
RETURN
COUNTROWS ( newtable )
  • Ilse_ScpDt's avatar
    Ilse_ScpDt
    Icon for Helper I rankHelper I
    4 years ago

    Hi,

     

    Thanks for the quick reply. It is working, but is it also possible to create a measure to count the count? Because I would like to filter the result on different variables from the table, but that (ofcourse) isnt working now. 

     

    Kind regards,

    Ilse

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Please check the below picture and the attached pbix file.

      It is for creating measures.

       

       

      Count measure: =
      VAR currentVAC =
      MAX ( Data[Vac] )
      VAR newtable =
      FILTER ( ALL ( Data ), Data[Vac] = currentVAC )
      RETURN
      IF ( HASONEVALUE ( Data[Vac] ), COUNTROWS ( newtable ) )
       
      Count2 measure: =
      VAR currentCount = [Count measure:]
      VAR newtable =
      FILTER (
      SUMMARIZE ( ALL ( data ), Data[Vac], Data[Count CC] ),
      Data[Count CC] = currentCount
      )
      RETURN
      COUNTROWS ( newtable )
      • Ilse_ScpDt's avatar
        Ilse_ScpDt
        Icon for Helper I rankHelper I
        4 years ago

        uHi,

         

        This measure is working the same as the calculated column, it doesnt react right on a filter. 

         

        I added a column Type to illustrate:

        When filtering on Type D (or B) you would like to see 1 for Count and 1 for Count2, but instead it will show 2 for Count2 (because that is the result for the total table):

         

        How can I make this work?