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.
Greetings,
Brand new to PBI here. I am trying to create a measure that counts "ID" column based off of "YEAR" column. HOWEVER, another column ("TERM") has different codes before 2016 than after. My company started changing codes that year based off of reporting changes.) Codes for pre-2016 are 5, 10, 20, and 30. Codes for 2016 and after are 20, 30, 35, and 40. (20 and 30 mean the same time period, so that does not need to change, but the difference in the other numbers indicates different time ranges.)
One small hitch: 2016 has both sets of codes associated with it -- but I only need to use 20, 30, 35 and 40 for the count within that year.
In short, I'm trying to find a way to have a consistent, distinct count of "ID" by "YEAR", and then filtering by the proper "TERM" code depending on the year. Pre-2016 will use codes 5, 10, 20, and 30 for the distinct count; 2016 and after will use codes 20, 30, 35, and 40.
------- Edited Here --------
Thank you kindly @Anonymous and @az38 for the quick responses -- and below is a sample dataset and then a desired outcome. I color coded who would drop and who would stay in the count, and provided details below. Definitely let me know if I can clarify further.
TABLE1
ID | YEAR | TERM |
0001 | 2015 | 5 |
0002 | 2015 | 10 |
0003 | 2015 | 20 |
0004 | 2015 | 30 |
0005* | 2016 | 5 |
0006* | 2016 | 10 |
0007 | 2016 | 20 |
0008 | 2016 | 30 |
0009 | 2016 | 35 |
0010 | 2016 | 40 |
0011 | 2017 | 20 |
0012 | 2017 | 30 |
0013 | 2017 | 35 |
0014 | 2017 | 40 |
0014** | 2017 | 30 |
0014** | 2017 | 40 |
0001*** | 2017 | 40 |
The distinct count for each year would be as follows:
2015 = 4 IDs.
2016 = 4 IDs. (The two IDs indicated with a single asterisk (*) are dropped due to the phased out codes occurring in that year ("5" and "10"). These codes do not exist after 2016 and only need to be excluded from this 2016.
2017 = 5 IDs. (The double asterisk (**) is dropped due to ID duplication within the same year. There are numerous duplicates in the actual dataset, so a distinct count would be needed here.) ***ID 0001 is counted since this person is present in a different year (2015), but it is not present within the same year. I failed to mention this in the original post.
Thank you again for any help!
------- END EDITS --------
All columns are from the same table name (TABLE1).
Solved! Go to Solution.
maybe you should try a measure like
Measure = CALCULATE(DISTINCTCOUNT(Table1[ID]),
FILTER(ALL(Table1), Table1[YEAR]=SELECTEDVALUE(Table1[YEAR]) && (Table1[TERM]>10 || Table1[YEAR]<2016)))
Thank you - I did look into this one after your reply, but due to there being duplicated rows, counting the raw number of rows would yield an excessive number of IDs. (I did not mention there were duplicates in the original posting, so that is definiely my bad for lack of clarity.) As such, it has to be a distinct count of IDs. I also edited the original post with a sample dataset and how the count would come out. Cheers!
Please share sample data and expected output.
Thanks,
PRavin
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |