Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have a table (bv) that contains a list of usrIDs, values, and dates.
I am displaying the average of these values across a line graph and would like to filter to show only the values for a "recurrent" group, so users who have a bvValue in 2021 AND 2022. It seems very straightforward but I've tried a few different things and just can't seem to figure it out.
In the below example, the only values that would show as part of the recurrent group would be usrID '2'.
The goal is to add it as a filter or as another y-axis to this graph
Solved! Go to Solution.
Hi,
These calculated column formulas works
Year = YEAR(Data[bvTS])
Same cohort = and(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[usrID]=EARLIER(Data[usrID])&&(Data[Year]=2021||Data[Year]=2022))),CALCULATE(DISTINCTCOUNT(Data[Year]),FILTER(Data,Data[usrID]=EARLIER(Data[usrID])))=2)
Hi,
Your goal is not clear. If you want to check consecutive occurence over years, then why should you have months on the X-axis - shouldn't you only have years there. Show your expected result in a simple Table format. Also, share data in a format that can be pasted in an MS Excel file.
Hi,
I have months on the X-axis because I want to show their trend over time month to month, not just year to year. However, I only want to present data for members who had a value in the years 2021 and 2022. The data I am presenting is PHI so it is useful to show monthly trends as members can have a value for every month. The data I have contains thousands of users, so showing that monthly data gives us an idea of the population's health trend.
Goal:
Filter data to only show values (bvValue) for usrID's that have a bvValue with a timestamp (bvTS) in 2021 and 2022. If a usrID has bvValues in just 2021 and not 2022, I don't want that usrID to be presented on the line graph.
It could look something like this. I only want to show bvValues for usrID's that have "Y" for the recurrent cohort, but I'm not sure how to calculate the recurrent cohort (bvValue in 2021 and 2022).
usrID | bvValue | Biometric Field | bvTS | Recurrent Cohort |
1 | 155 | weight | 1/28/2021 | Y |
1 | 151 | weight | 2/4/2021 | Y |
1 | 146 | weight | 11/10/2021 | Y |
1 | 140 | weight | 2/5/2022 | Y |
2 | 267 | weight | 5/8/2021 | N |
2 | 288 | weight | 7/10/2021 | N |
3 | 200 | weight | 8/10/2021 | N |
4 | 250 | weight | 11/21/2022 | N |
5 | 180 | weight | 12/15/2021 | Y |
5 | 184 | weight | 2/10/2022 | Y |
5 | 194 | weight | 3/4/2022 | Y |
5 | 191 | weight | 4/5/2022 | Y |
6 | 177 | weight | 1/20/2021 | Y |
6 | 179 | weight | 2/17/2021 | Y |
6 | 175 | weight | 3/27/2021 | Y |
6 | 173 | weight | 4/5/2021 | Y |
6 | 168 | weight | 7/7/2021 | Y |
6 | 165 | weight | 9/10/2021 | Y |
6 | 160 | weight | 11/18/2021 | Y |
6 | 164 | weight | 1/7/2022 | Y |
6 | 162 | weight | 3/5/2022 | Y |
7 | 350 | weight | 11/20/2022 | N |
8 | 200 | weight | 5/5/2021 | N |
8 | 220 | weight | 10/18/2021 | N |
Hi,
These calculated column formulas works
Year = YEAR(Data[bvTS])
Same cohort = and(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[usrID]=EARLIER(Data[usrID])&&(Data[Year]=2021||Data[Year]=2022))),CALCULATE(DISTINCTCOUNT(Data[Year]),FILTER(Data,Data[usrID]=EARLIER(Data[usrID])))=2)
Thank you!!! Works great!
You are welcome.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |