COUNTX(currentgroup()) add column with filter

[zipke]

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

[Whibley92]

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

[v-lili6-msft]

hi, @zipke 

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

[zipke]

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]

hi, @zipke 

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

Best Regards,

Lin 

[zipke · Accepted solution]

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

[v-lili6-msft]

hi, @zipke 

You're welcome.

It's pleasant that your problem has been solved, could you please mark the reply as Answered.  

Best Regards,

Lin