Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I have a table that includes form data, form metadata, questions and answers in the same table. Metadata values for each record may differ for the same metadata, on the same form. The end goal is to have a table listing the distinct values of formID and 1 value for each metadata.
My approach is this:
1. In Edit Queries:
- add column 'CustomColumn'
- set the calculated value (this is the formula I'm trying to create)
2. In powerBI desktop, create a table with SUMMARIZE(tableA, FormID, FormCompleted) but I only want 1 row for each formID.
Here is a sampling of data:
FormID FormCompleted (metadata) CustomColumn
1 Yes Yes
2 Yes Yes
3 No No
4 Yes Yes
4 No Yes
The column "FormCompleted" for FormID 4 has 2 different values; I need to set the value of CustomColumn to 'Yes' whenever this happens. Does anyone know how this can be accomplished?
Is there a way to calculate the custom column value based on the count of rows in a group by?
Hopefully that makes sense.
Thanks for any assistance that can be provided.
Solved! Go to Solution.
Since youll use SUMMARIZE you can create this DAX Column
Custom DAX Column = IF ( CALCULATE ( COUNTA ( 'Table'[FormID] ), ALLEXCEPT ( 'Table', 'Table'[FormID] ) ) = 1, 'Table'[Form Completed], "Yes" )
Hope this helps!
Since youll use SUMMARIZE you can create this DAX Column
Custom DAX Column = IF ( CALCULATE ( COUNTA ( 'Table'[FormID] ), ALLEXCEPT ( 'Table', 'Table'[FormID] ) ) = 1, 'Table'[Form Completed], "Yes" )
Hope this helps!
Thanks Sean,
I tried your formula, but am receiving this error now:
'A single value for column 'FormCompleted' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'
I added it as a calculated column on the SUMMARIZEd table. Any thoughts on what else I'm doing wrong here?
COMPLETE = IF ( calculate ( counta ( Forms[FormID] ), ALLEXCEPT( Forms , Forms[FormID] ) ) = 1, Forms[Completed], "Yes")
Thanks
Thanks Sean!
I was putting the column on my summarized table.. :smileyfrustrated
Your formula worked. Thanks for the help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |