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.
I started with Power BI Desktop yesterday and even after reading many forum posts and trying to use Measures I cannot seem to achieve what I beleive is fairly simple.
From the example table below I wish to create a bar chart of user sign-ups per month. YearMonth is a calculated field and I want to see a bar chart with 2021-01, 2021-02, 2021-03 etc on the x access and a count of signs ups in that period on the y axis.
I've tried to group by YearMonth and count the entries for that group but simply cannot figure out how to do it. If anyone can help that would be appreciated.
YearMonth | Name | CreatedDate |
2021-02 | Lucy | 01/02/2021 16:01 |
2021-02 | Tracy | 01/02/2021 15:58 |
2021-02 | Qureshi | 01/02/2021 12:44 |
2021-02 | Nargis | 01/02/2021 11:55 |
2021-02 | Teena | 01/02/2021 11:37 |
2021-02 | Sandeep | 01/02/2021 08:48 |
2021-02 | Sandeep | 01/02/2021 08:48 |
2021-02 | RK | 01/02/2021 03:35 |
2021-01 | Jimmy | 30/01/2021 11:55 |
2021-01 | Febina | 30/01/2021 11:10 |
2021-01 | Paul | 29/01/2021 16:50 |
2021-01 | Thomas | 29/01/2021 15:59 |
Solved! Go to Solution.
Hi @TJM_UK
ah ok you're using Direct Query. OK a different approach. Create a table from the data you get from Azure. Click New Table in the Ribbon then use this code
Year_Month = SELECTCOLUMNS('Table', "YearMonth", FORMAT([CreatedDate], "yyyy-mm"), "Name", [Name])
Creates this
Then use that table to create the visual
Regards
Phil
Proud to be a Super User!
Dear Phil,
Thank you so much for your help with this. It has been interesting seeing the differences with DirectQuery and creating a new table in BI based on that data.
It took me a while to figure out that dragging the Name field to the axis magically created the count of name. I was trying to find where the formula for that was stored.
No worries. Yes, dragging a text field into values will auto-count them.
Cheers
Phil
Proud to be a Super User!
Hi @TJM_UK
ah ok you're using Direct Query. OK a different approach. Create a table from the data you get from Azure. Click New Table in the Ribbon then use this code
Year_Month = SELECTCOLUMNS('Table', "YearMonth", FORMAT([CreatedDate], "yyyy-mm"), "Name", [Name])
Creates this
Then use that table to create the visual
Regards
Phil
Proud to be a Super User!
Hi @TJM_UK
Can you please supply your PBIX file so I can check it.
Regards
Phil
Proud to be a Super User!
Yes of course. The backend is an Azure SQL database. Not sure if that is the issue. I can't seem to find how to create the Count of Name variable where right clicking gives this:
Hopefully the link here works. Not sure how accessing the back-end Azure database will work for you.
Thank you so much !! The PBIX is really helpful but I must be missing something somewhere because my group doesn't count like yours.
As you can see my YearMonth seems to be treated as individual values rather than a group.
Hi @TJM_UK
Download this sample PBIX with the visual below
Like this
Regards
Phil
Proud to be a Super User!
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 |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |