Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 )
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |