Grouping into a new table

[Anonymous]

Hello,

I have in the moment a "blackout"!

I need help to following problem:

I have a table:

date	Capa	Closing 30 Day
2021-12	123	yes
2021-11	124	yes
2021-11	532	no
2021-11	36	yes
2021-11	574	yes
2021-12	45875	no
2021-12	88	yes
2021-12	546	yes
2021-12	463	yes
2021-12	364	no
2021-12	4374	yes
2021-08	47	yes
2021-08	58	yes
2021-08	589	yes
2021-08	6964	no
2021-08	489	yes
2021-08	499	yes

I want to create the followingtable with a DAX function

date	Closing 30 Day yes	Closing 30 Day no
2021-08	5	1
2021-11	3	1
2021-12	5	2

Thanks for help

Friedbert

[Jihwan_Kim]

Hi,

Please try the below.

It is for creating a new table.

New Table = 
GENERATE (
    VALUES ( Data[Date] ),
    {
        ( CALCULATE (
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( Data, "@Closing30dayYes", Data[Closing 30 Day] ),
                    [@Closing30dayYes] = "Yes"
                )
            )
        ), CALCULATE (
            COUNTROWS (
                FILTER (
                    SELECTCOLUMNS ( Data, "@Closing30dayNo", Data[Closing 30 Day] ),
                    [@Closing30dayNo] = "No"
                )
            )
        ) )
    }
)

[Anonymous]

Thank you very much, it woorks.

[vapid128 · Accepted solution]

Table5 = 
SUMMARIZE(
    Table4,
    Table4[date],
    "Closing 30 Day yes",CALCULATE(COUNTROWS(Table4),FILTER(Table4,Table4[Closing 30 Day]="yes")),
    "Closing 30 Day no",CALCULATE(COUNTROWS(Table4),FILTER(Table4,Table4[Closing 30 Day]="no"))
)

[Anonymous]

Thank you very much, it woorks.