Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |