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.
Hi!
I need your help, I have spent a long time in this project and I haven't found the solution still.
I need to show all the times is searched a terms in the web. Por example, if the user search the name "Jim", in the data base is saved:
Name Date
Jim '01/01/2016'
I would like count how count often is searched each name. If I have in my Datebase:
Name Date
Jim '01/01/2016'
Tom '12/23/2002'
Jim '06/19/2006'
Mary '03/14/2004'
I can return
Name Count
Jim 2
Tom 1
Mary 1
With this query:
SELECT name, COUNT(*) FROM myTable GROUP BY name ORDER BY COUNT(*)
BUT I would like keep the dates because I need to show the quantity of search in some dates (like the user want to select). The dates is no important for me show it in my grid table in Power BI Desktop; the dates is important for use my Slicer.
Maybe it is no the correct title for my problem... I'm sorry for my English, it isn't my native laguage.
I'll appreciate your help.
Thanks in advance!
In addition to ankitpatira's solution, you can just import the table into Power BI with the SQL query like below.
SELECT name, date FROM myTable
Then you should be able to create a measure like below to calculate the searched count of each name, and show it in the report. In the meantime, you can still use Date as a Slicer.
CountOfName = COUNTA(myTable[Name])
Regards
@dsilveira In that case your sql query would be as below and you will get count by dates. Then in slicer for power bi destkop drop date field and click on dropdown icon and instead of date hierarchy choose just date.
SELECT name, date, COUNT(*) FROM myTable GROUP BY name, date ORDER BY COUNT(*)
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |