Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have data that looks like this:
EmploymentID | PersonID |
1 | A |
2 | A |
3 | B |
4 | C |
5 | D |
6 | E |
7 | F |
8 | G |
9 | G |
10 | G |
I want to count how many employments each person has, which would result in something like the following:
EmploymentID | PersonID | No. Of Employments |
1 | A | 2 |
2 | A | 2 |
3 | B | 1 |
4 | C | 1 |
5 | D | 1 |
6 | E | 1 |
7 | F | 1 |
8 | G | 3 |
9 | G | 3 |
10 | G | 3 |
The challenge is, I want the No. Of Employments value to be effected by filters, so I need it to be a measure, rather than a calculated column.
This is the formula I used to create a calculated column:
No. Of Employments = CALCULATE ( COUNT ( [PersonID] ), FILTER ( Dim_Employments, [PersonID] = EARLIER ( [PersonID] ) ) )
However, this does not work as a measure; it returns the following error message:
"EARLIER/EARLIEST refers to an earlier row context which doesn't exist."
Any thoughts?
Thanks!
Solved! Go to Solution.
Hi @bullius,
Add you columns to the all except.
The new measure would be something like this:
Measure = CALCULATE( COUNT(Table1[PersonID]), ALLEXCEPT(Table1,Table1[PersonID],Table1[status]))
as you can see below the top half is raw data and measure without interacting with slicer, bottom part table is filter by status B
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @bullius,
Use the following measure:
N.º Employments =
CALCULATE ( COUNT ( Table1[PersonID] ), ALLEXCEPT ( Table1, Table1[PersonID] ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHow All Except Function is working in that function
Hi @Anonymous ,
The ALLEXCEPT function removes all context filters in the table except filters that have been applied to the specified columns. In this case when making the count it will only keep the ID and Status filter on the table.
In this case the calculation is picking up all users with the selected status B.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix,
I created a matrix with the measure in the values bucket, but it does not seem to be affected by visual level filters...
What do you mean to be affected by visual level filters?
What type of iteractions you want to have and expected result.
Regards
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMy data also includes other fields like Status (Active/Inactive).
An example of the functionality I want would be:
I want to be able to filter the matrix to only include active emplyments and the No. of Employments per PersonID to change.
Make sense?
Hi @bullius,
Add you columns to the all except.
The new measure would be something like this:
Measure = CALCULATE( COUNT(Table1[PersonID]), ALLEXCEPT(Table1,Table1[PersonID],Table1[status]))
as you can see below the top half is raw data and measure without interacting with slicer, bottom part table is filter by status B
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
i'm trying to do something similar to this. Would you know how to create the same output but without having the Person ID displayed on the table? i would like a dax expression that automatically looksup the corresponding personID related to the ID and returns a count. so final table would look like
ID Count
1 2
2 2
3 1
4 1
5 1
6 1
7 1
8 3
9 3
10 3
with the above DAX expression, i am getting a 7 in every row. any help appreciated, thanks
Hi @gsingh67 ,
What do you mean by not having the person ID displayed in the table?
The calculation of measures is based on context so if you don't have in this case the person ID the result will be different.
You also mentioned that you have 7 in all rows, what is the value for each row? is it another column on your data?
Dou you want to have a table only with the values on the count measure?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks again!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |