Create Subset of Data - Group by Month

[Deepak3Arora]

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

1	Open	January
2	Open	January
3	Open	February
4	Closed	January
5	Closed	February
6	Open	March
7	Open	April
8	Closed	April
9	Closed	April
10	Closed	May

Needed as 

Month      Open Closed

January	2	2
February	1	1
March	1	0
April	1	2
May	0	1

Regards,

Deepak

[v-kkf-msft · Accepted solution]

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.

[Anonymous]

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

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

[Deepak3Arora]

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

[amitchandak]

@Deepak3Arora , Use matrix visual,

Month on row, Status on column and count of id values

[Deepak3Arora]

Hello Amit,

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

Regards,

Deepak