Forum Discussion

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

How to group by ?

Hi,

sorry for this basic question. I hang around the community before posting. I found some help, but i was not able to implement these advice in my case.

 

So, i have a following table :

 

I would like to generate a report like this one :

Model , datemonth, CPU, Frequency, RAM

group by model, datemonth, CPU, Frequency, RAM with count of unique model

ie :

Proliant ML350GEN10,      2020-2-11   Intel,  ,2500    ,  512   , 4

Proliant ML350GEN10,      2020-3-14   Intel,  ,2500    ,  512   , 1

 

I tryed to use SUMMARIZE function with no luck.

 

I have the following error :

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

if i put 1 element, several are required, and if i put several, another error ...

 

Can you give me some advice ?

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hello @fabd ,

    I'm sorry for my mistake. Please try the formula below.

    Table 2 = 
SELECTCOLUMNS (
    'Table',
    "Model", 'Table'[Model],
    "DateMonth", 'Table'[datemonth],
    "CPU", 'Table'[CPU],
    "Frequency", 'Table'[Frequency],
    "RAM", 'Table'[RAM],
    "DistinctCount", CALCULATE (
        DISTINCTCOUNT ( 'Table'[Model] ),
        ALLEXCEPT ( 'Table', 'Table'[datemonth] )
    )
)

    3.PNG

    I changed the sample data. For more information, see the attachment.

9 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Just Try Creating MAtix Table use rows and Columns to accomodate fields and let me know if any issue and paste the snapshot to see what else you require.

    If this is an answer then you can mention this as an answer and share your Kudoes

    Regards

    Vijay Perepa

    you can watch my Videos on www.youtube.com/perepavijay

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    fabd , Can you share the summarize you have used.

     

    What is left for Distinct count after selecting model

    summarize(Table, Table[model], Table[datemonth], Table[CPU], Table[Frequency], Table[RAM] ,"Unique Model", distinctCOUNT(Table[Name]))

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

      Hello guys,

       

      Following your advice, here is the result with the summarize()

       

      For the matrix, this is only what i successed to do

       

       

      I would like something like this in a simple table that i could export to csv after :

       

      serverModelName,manufacturer,numCpus,cpuType,cpuGhz,memGb,numberOfServers,dateOfPurchase,pricePerServer,
      batchType,dataCenterName

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

    Hi fabd ,

    Do you want to create a new table? If so, you could try like this:

    Table 2 =
SELECTCOLUMNS (
    'Table',
    "Model", 'Table'[Model],
    "DateMonth", 'Table'[datemonth],
    "CPU", 'Table'[CPU],
    "Frequency", 'Table'[Frequency],
    "RAM", 'Table'[RAM]
)

     

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

      Hello Xue,

       

      How do you make a group by to count number of Model for each year/month ?

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

        Hello @fabd ,

        I'm sorry for my mistake. Please try the formula below.

        Table 2 = 
SELECTCOLUMNS (
    'Table',
    "Model", 'Table'[Model],
    "DateMonth", 'Table'[datemonth],
    "CPU", 'Table'[CPU],
    "Frequency", 'Table'[Frequency],
    "RAM", 'Table'[RAM],
    "DistinctCount", CALCULATE (
        DISTINCTCOUNT ( 'Table'[Model] ),
        ALLEXCEPT ( 'Table', 'Table'[datemonth] )
    )
)

        3.PNG

        I changed the sample data. For more information, see the attachment.