The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all,
There's something I'm trying to do in DAX using a measure which intuitively feels like it should be possible.
I have two tables, one a list of users (with user IDs), the second table contains a list of tests which were taken. with information on when the test was taken, the user who took the test etc.
I'd like a table which shows the year on the rows (2012, 2013 etc.) the value I'd like to display is a count of the number of users who completed a certain number of tests in that year.
I hope that makes sense. The way I've tried to do this is to create a calculated column (testsnum) in the user's table. In this column I count the number of tests each user took by counting the rows of the test table where user IDs are equal.
In the measure I use a countx to count the number of rows (in the user table) where 'testnum' is at least a certain value.
The problem I have is that I can't easily summarise by year etc. I was hoping that when I do this, the calculated column testnum would be recalculated to only include tests in a certain year etc. However, as I understand it is that the calculated column is 'static'. Is there a way to do this with measures alone? What am I doing wrong?
Thanks,
Wiredchop
Solved! Go to Solution.
Hi @Anonymous,
You can refer to below formula to create a summary table with you wanted data.
Table formula:
Table = var temp= SUMMARIZE(Test,[Date].[Year],[User ID],"Count",COUNT(Test[Date])) return SUMMARIZE(temp,[Date].[Year],"Count >= 2",COUNTX(FILTER(temp,[Date].[Year]=EARLIER([Date].[Year])&&[Count]>=2),[Count])+0)
Regards,
Xiaoxin Sheng
Having some trouble visualizing this from the description, can you post some sample data and a sample of your desired output?
Apologies, i thought this may be a little incoherent.
I've mocked up a very simple example in Excel, I hope it clarifies things. To be clear, the 'value' I'm after in the final table is a count of the numbers of users that have completed a specific number of tests. I could then filter by year or any other filter in the future.
Any advice much appreciated.
Thanks,
Simon C
Hi @Anonymous,
You can refer to below formula to create a summary table with you wanted data.
Table formula:
Table = var temp= SUMMARIZE(Test,[Date].[Year],[User ID],"Count",COUNT(Test[Date])) return SUMMARIZE(temp,[Date].[Year],"Count >= 2",COUNTX(FILTER(temp,[Date].[Year]=EARLIER([Date].[Year])&&[Count]>=2),[Count])+0)
Regards,
Xiaoxin Sheng
Excellent, thanks very much for that. The summarise function has fantastic power.
I settled on a simpler solution that yours. I created an additional table based on your intemediary variable. After that I can use a visualisation and filters to summarise the data, this also has more flexibility.
You're on the right path, but with the current structure of your relationships, you're not able to include the Year filter you want.
I would add a date table and create the following relationships:
From there, to count the completions, create a measure in the "TESTS" table as follows: 'Test Completions': COUNTROWS(TESTS).
Using the Table Visualization, drag your Year, User ID, and [Test Completions] measure to achieve your desired result. Please note I added the User ID as a field on the table because I thought it might be the next step in your analytical journey. Also, tip when using Year as a categorical value: be sure to disable any aggregations on it that PowerBI will instinctively try to perform.
Thanks for your help, I'm trying to understand your suggested approach. As far as I understand, won't the measure just tell me how many tests were taken in a particular year? I want to know how many users took 'X' tests in that year. Is it possible using your approach?