Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Represent count across another count

Hi, I have a table that has the account id and the device id associated with the account. One account can have multiple devices. I want to represent how many accounts have 1 devices, how many accounts has 2 devices and so on. I am not able to represent the count(account) across count(device) in a bar graph in direct query mode. The sql will look something like this: select A.dvc, count(A.account) (select account, count(devices) as dvc from table) A Thanks

Need Help

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

    You may drag the account id and device id columns as below when creating the bar graph.

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This didn't work, it is just giving two count, the device count is not coming in the x axis.

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous 

        If you want to get the count and use it in x-axis.You may get the count columns and use them.I would suggest you create a new table with SUMMARIZE function like below.Then drag the count columns to x-axis.

        Table2 =
SUMMARIZE (
    'Table',
    'Table'[account id],
    'Table'[device id],
    "Count1", COUNT ( 'Table'[account id] ),
    "Count2", COUNT ( 'Table'[device id] )
)

        For further,please refer to below article.

        https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

        For 3rd party Links:
        Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

        Regards,