Forum Discussion

Deepak3Arora's avatar
Deepak3Arora
Frequent Visitor
4 years ago
Solved

Create Subset of Data - Group by Month

Hi Team,

 

Hw can I create a DAX/any other way in Power BI to create a subset of Data as a Table in the form of Matrix.

 

I Dont need to show it as Visualization but Have this Data in Table Form to perform further round of actions on them.

 

Base Data is

ID       Status     Month

1OpenJanuary
2OpenJanuary
3OpenFebruary
4ClosedJanuary
5ClosedFebruary
6OpenMarch
7OpenApril
8ClosedApril
9ClosedApril
10ClosedMay

 

Needed as 

Month      Open Closed

January22
February11
March10
April12
May01

 

Regards,

Deepak

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Deepak3Arora ,

     

    Please create the new table.

     

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Month],
    "Open",
        CALCULATE ( COUNT ( 'Table'[ID] ), 'Table'[Status] = "Open" ) + 0,
    "Closed",
        CALCULATE ( COUNT ( 'Table'[ID] ), 'Table'[Status] = "Closed" ) + 0
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Deepak3Arora ,

     

    Please create the new table.

     

    Table 2 =
SUMMARIZE (
    'Table',
    'Table'[Month],
    "Open",
        CALCULATE ( COUNT ( 'Table'[ID] ), 'Table'[Status] = "Open" ) + 0,
    "Closed",
        CALCULATE ( COUNT ( 'Table'[ID] ), 'Table'[Status] = "Closed" ) + 0
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Deepak3Arora's avatar
      Deepak3Arora
      Frequent Visitor
      4 years ago

      Hello Amit,

       

      I need to save the data as a subset and not visualization.

       

      Regards,

      Deepak

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Open = CALCULATE(COUNTROWS(your_table),your_table[Status]="Open")
    closed = CALCULATE(COUNTROWS(your_table),your_table[Status]="Closed")


    • Deepak3Arora's avatar
      Deepak3Arora
      Frequent Visitor
      4 years ago

      Hi Suhaib,

       

      Sorry If i confused you but I need to have this data in a Table to perform more actions on it rather than showing in the visualization.

       

      Regards

      Deepak