Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
hi, @zipke
Could you share your sample pbix file or screenshots about the relationship?
Best Regards,
Lin
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |