need to create a new table with existing table

[Herndon_powerbi]

Hi, I have a table like below,

I want to create a new table based on the above table

   Please let me know how to create the new table with DAX. Thanks in advance.

[VahidDM · Accepted solution]

Hi @Herndon_powerbi 

Try this code:

Table 2 =
VAR _A =
    SUMMARIZE ( 'Table', 'Table'[Student], "count-id", COUNTA ( 'Table'[Student] ) )
VAR _B =
    ROW (
        "student", "movie1-yes",
        "count-id", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[movie1] = "yes" )
    )
VAR _C =
    ROW (
        "student", "movie2-no",
        "count-id", CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[movie2] = "no" )
    )
RETURN
    UNION ( _A, _B, _C )

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Herndon_powerbi]

Hi,

     Your solution is correct but I need to use several page filters for this visual. Because I am using the above-mentioned DAX, none of the page filters are applicable. Values are not changing. I want to know how can I overcome this error. Thanks in advance

[Herndon_powerbi]

Hi,

     Please ignore my previous requests, your query is working fine.

Thanks,

Herndon_Powerbi

[Herndon_powerbi]

Hi,

      Your solution is correct but it is counting blank values in the column.

I don't want blank values. Please help me.

Thanks in advance,

 @Herndon_powerbi 

[Herndon_powerbi]

Hi,

       Thanks for your solution. I have 4 filters for this query all from different tables. I included them in the page filter. but it seems they are not effective. what should I do to make them effective?

Thanks

[smpa01]

@Herndon_powerbi 

Calculated Table=
VAR _1 =
    ADDCOLUMNS (
        VALUES ( 'Table'[Student] ),
        "count-id", CALCULATE ( COUNT ( 'Table'[Student] ) )
    )
VAR _2 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            FILTER ( VALUES ( 'Table'[movie1] ), 'Table'[movie1] = "yes" ),
            "count-id", CALCULATE ( COUNT ( 'Table'[movie1] ) )
        ),
        "Student", [movie1],
        "count-id", [count-id]
    )
VAR _3 =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            FILTER ( VALUES ( 'Table'[movie2] ), 'Table'[movie2] = "yes" ),
            "count-id", CALCULATE ( COUNT ( 'Table'[movie2] ) )
        ),
        "Student", [movie2],
        "count-id", [count-id]
    )
RETURN
    UNION ( _1, _2, _3 )