Forum Discussion

dsilveira's avatar
dsilveira
Icon for Advocate I rankAdvocate I
10 years ago

Problem with GROUP BY and show dates in Slicer

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!

 

 

 

 

 

 

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks

2 Replies

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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(*)

     

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    dsilveira

     

    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