Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

COUNTX(currentgroup()) add column with filter

Hi all,

 

I have a table with different IDs linked to a specific Category. These IDs all have an Entry Date stored in a different (related) table.

 

I created an overview table from a different table with the GROUPBY() and COUNTX function.

I managed this with the code:

Categorization overview = GROUPBY(Categorisation,Categorisation[Category],"Count",COUNTX(CURRENTGROUP(),Categorisation[Category]))

This is the table: the green column is the result of the above code. This works OK.

 

 

The two columns in red are the columns I am trying to add.

 

Add column 1:

I need a column that only counts the IDs with an Entry Date > 01012019. How can I add this column? I tried to add an IF statement but when I do this I get a rondom number that is the same for each row.

 

Add column 2:

I then also need a column with the percentage of this column 1 to the initial column which contains all IDs. I want to show how much percent of the total IDs in that category has recently been created (based on the Entry Date).

 

Thanks for your help!

 

Zipke

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi v-lili6-msft ,

     

    It is not that easy to share my relationships because it is a larger complex file. But in manged to solve the problem in the meantime! I used a different code and it works!

    I created the table with only the categories and then added a Count column and a Count column for the IDs.

    Table creation:

    Categorisation overview = GROUPBY(Categorisation,Categorisation[Category])

    Count of IDs per category:

    Count = CALCULATE(
    COUNTROWS(Categorisation),
        FILTER(Categorisation,Categorisation[Category]=EARLIER([Category])
        )
)

    Column 1:

    New IDs count = CALCULATE(
    COUNTROWS(Categorisation),
        FILTER(Categorisation,Categorisation[Category]=EARLIER([Category])),
        FILTER(RELATEDTABLE('Entry Dates'),'Entry Dates'[Entry date]>=20181201)
        )

    Thanks for your help though!

     

    Regards,

    Zipke

6 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, Anonymous 

    You may try this simple way:

    Step1:

    Create the relationship between Categorisation table and new overview table by category column.

    Step2:

    Then use this formula to add new column

    Column 1 = CALCULATE(COUNTA('different (related) table'[ID]),FILTER(RELATEDTABLE('different (related) table'),'different (related) table'[Entry Date]>DATE(2019,1,12)))
    Column 2 = 'Categorization overview'[Column 1]/'Categorization overview'[Count]

     

    Best Regards,

    Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-lili6-msft ,

       

      Thanks for your response. For column 1, I do get a count, but it shows me the total on each row. It does not split up the value per category. I did create the relationship between the Categorisation table and the Entry Date table.

       

      How should I tweek the code?

       

      Thanks in advance

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Anonymous 

        Could you share your sample pbix file or screenshots about the relationship?

         

        Best Regards,

        Lin 

         

  • Whibley92's avatar
    Whibley92
    Icon for Helper II rankHelper II
    2 years ago

    Hello, I have a query related this - I have used the below to count the number of occurences of an id number in one column:

     

    occurrences = COUNTX( FILTER( 'Specialisations', EARLIER('Specialisations'[Person Number]) = Specialisations[Person Number]),Specialisations[Person Number])

     

    I want to count only those ids which have a certain course title. I'm not sure what the best way to do this is - maybe i should be using calculate?

     

    i.e count occurence of person number if course title = XXX