Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to Solution.
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/
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/
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
Hi,
Please ignore my previous requests, your query is working fine.
Thanks,
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,
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
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 )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |