Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

GroupBy and Having Clause

Hi,

 

How do I use the GroupBy and Having clause in my dataset? I have the following table:

  

[TableA]

Name    Completed  TaskNumber

Opp1     No               1

Opp1     No               2

Opp1     No               3

Opp1     No               4

Opp2     No               1

Opp2     No               2

Opp2     No               3

 

In SQL the statement would be this:

 

Select Name from [TableA]
GroupBy Name
Having Count(TaskNumber) > 3

 

 

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Thanks Matt but this didn't really do what I needed.

    In the end I did a Group By via the Query Editor and did a slice visualisation against the count.

     

    Cheers

3 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    Power BI doesn't work this way (I guess you know that).  Are you trying to see these records in a visual, such as a table?  If so, you need to write a measure that returns a non blank result when it passes your having test, and then place that in a visual along with the columns you want to see. A measure could be

     

    display = if(countrows(tableA))>=3,countrows(tableA))

     

    put name and the measure in a visual

    • mbommarito's avatar
      mbommarito
      Frequent Visitor
      9 years ago

      What If I want a sum of the subtotals that meet those rules?  Currenly I get a total of all rows combined.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks Matt but this didn't really do what I needed.

      In the end I did a Group By via the Query Editor and did a slice visualisation against the count.

       

      Cheers