creating a new table in dax based on 4 new calculated column

[hammadafridi]

Hi i am going to create a new table in which show the two column from the below attached image 

result:

In result it shows the count of prod_id against those column based on check.

like count of prod_id where bath column is bath and count of prod_id where shower column is shower and count of prod_id where large and small is true. 

bath 7 shower 5 large 4 small 2

 Please help me with it i want to do this through dax

[v-zhangti · Accepted solution]

Hi, @hammadafridi 

You can try the following methods.

Table2 = 
UNION (
    SUMMARIZE (
        'Table',
        "type", "shower",
        "count", COUNTAX ( FILTER ( 'Table', [shower] = "shower" ), [shower] )
    ),
    SUMMARIZE (
        'Table',
        "type", "bath",
        "count", COUNTAX ( FILTER ( 'Table', [bath] = "bath" ), [bath] )
    ),
    SUMMARIZE (
        'Table',
        "type", "large",
        "count", COUNTAX ( FILTER ( 'Table', [large] = TRUE ), [large] )
    ),
    SUMMARIZE (
        'Table',
        "type", "small",
        "count", COUNTAX ( FILTER ( 'Table', [small] = TRUE ), [small] )
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@hammadafridi , In power query, Unpivot and group by

https://radacad.com/pivot-and-unpivot-with-power-bi

in Dax

Union and summarize

union(

summarize(Table, "Count" , count(Table[Bath]) ,"Column", "Bath" ) ,

summarize(Table, "Count" , count(Table[Shower]) ,"Column", "Shower" ) ,

summarize(Table, "Count" , count(Table[large]) ,"Column", "large" ) ,

summarize(Table, "Count" , count(Table[small]) ,"Column", "small" )

)

You can use distinct count if needed