Forum Discussion

beckyconning's avatar
beckyconning
Icon for Helper I rankHelper I
6 years ago
Solved

Subsequent GROUP BYs in Power BI

I'm trying to figure out which analysis tools to ask my team to use. As an example task I'm trying to work out from a FDA dataset what affects different combinations of drugs may have. In SQL I...
Need Help
  • beckyconning's avatar
    beckyconning
    6 years ago

    I was a little confused. You posted some code above but thats 414 characters whereas the original SQL is 299 characters.

    Also with minor changes the SQL works in every SQL dialect whereas the code you posted appears to be specific to Power BI.

    I thought the point of tools like Power BI was to allow business users to analyse data whilst writing less code than would be required without Power BI and that this was at odds with this.

    It seems that Power BI goes most of the way in this case though.

    When I right click on my table and click "Edit query"

    And I right click a blank value in the "Active substance" column

    And click "Text Filters"
    And click "Does not equal"
    And I click "Group by"

    And I click "Advanced"

    And I provide "Report id" as the first grouping
    And I click "Add grouping"
    And I provide "Reaction" as the second grouping

    And I provide "Active substances" as the "New column name"
    And I provide "Min" as the "Operation"
    And I provide "Active substance" as the "Column"

    And I click "OK"
    And I click "View"

    And I click "Advanced editor"
    And I replace `List.Min([Active substance])` with `Text.Combine(List.Distinct([Active substance]), ", ")`
    And I click "Done"

    And I click "Group by"

    And I click "Advanced"

    And I provide "Active substances" as the first grouping
    And I click "Add grouping"

    And I provide "Reaction" as the second grouping
    And I provide "Count" as the "New column name"
    And I provide "Count Rows" as the "Operation"

    And I click "OK"

    And I sort the table by "Count" descending

    And I click "Home"
    And I click "Close & Apply"
    And I click "Table"

    And I click the checkbox next to "Active substances"
    And I click the checkbox next to "Reaction"
    And I click the checkbox next to "Count"

    Then I see the results I expected.

     

    It would be nice to be able to do the `List.Distinct` and the `Text.Combine` from the UI rather than the code editor but clearly 53 characters is an improvement over 299.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi beckyconning 

When i run your SQL query on my side, it shows an error.

To provide any suggestions, we need to confirm your requirement.

Could you describe your reuqiurment in words or show me the correct SQL statement?

 

Here is a example  i create in Power BI.

create a measure

Measure =
IF (
    MAX ( [Active substance] ) <> BLANK (),
    CALCULATE (
        CONCATENATEX (
            DISTINCT ( 'Adverse+reactions'[Active substance] ),
            [Active substance],
            ",",
            [Active substance], ASC
        ),
        ALLEXCEPT (
            'Adverse+reactions',
            'Adverse+reactions'[Report id],
            'Adverse+reactions'[Reaction]
        )
    )
)

Please check if it works on your side and give your expected result.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

beckyconning's avatar
beckyconning
Icon for Helper I rankHelper I
6 years ago

It’s a PostgreSQL query.

 

We start where the count of rows represents the number of active substances in each report * the number of reactions in each report.

 

Next we group all the rows where active substance is not null by report and reaction summarising the active substances into a single ordered string representing the combination of active substances in each report.

 

At this point the count of rows represents the number of reactions in each report.

 

We then group by combination of active substances and reaction. This removes the association with reports and allows us to count the frequency of each reaction to each combination of active substances.

 

The count of rows now represents the number of combinations of active substances * the number of reactions to those substances.

  • beckyconning's avatar
    beckyconning
    Icon for Helper I rankHelper I
    6 years ago

    I was a little confused. You posted some code above but thats 414 characters whereas the original SQL is 299 characters.

    Also with minor changes the SQL works in every SQL dialect whereas the code you posted appears to be specific to Power BI.

    I thought the point of tools like Power BI was to allow business users to analyse data whilst writing less code than would be required without Power BI and that this was at odds with this.

    It seems that Power BI goes most of the way in this case though.

    When I right click on my table and click "Edit query"

    And I right click a blank value in the "Active substance" column

    And click "Text Filters"
    And click "Does not equal"
    And I click "Group by"

    And I click "Advanced"

    And I provide "Report id" as the first grouping
    And I click "Add grouping"
    And I provide "Reaction" as the second grouping

    And I provide "Active substances" as the "New column name"
    And I provide "Min" as the "Operation"
    And I provide "Active substance" as the "Column"

    And I click "OK"
    And I click "View"

    And I click "Advanced editor"
    And I replace `List.Min([Active substance])` with `Text.Combine(List.Distinct([Active substance]), ", ")`
    And I click "Done"

    And I click "Group by"

    And I click "Advanced"

    And I provide "Active substances" as the first grouping
    And I click "Add grouping"

    And I provide "Reaction" as the second grouping
    And I provide "Count" as the "New column name"
    And I provide "Count Rows" as the "Operation"

    And I click "OK"

    And I sort the table by "Count" descending

    And I click "Home"
    And I click "Close & Apply"
    And I click "Table"

    And I click the checkbox next to "Active substances"
    And I click the checkbox next to "Reaction"
    And I click the checkbox next to "Count"

    Then I see the results I expected.

     

    It would be nice to be able to do the `List.Distinct` and the `Text.Combine` from the UI rather than the code editor but clearly 53 characters is an improvement over 299.