Set column value based on a set of records in table

[user10]

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.

[Sean · Accepted 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!

[user10]

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

[Sean]

First create the COLUMN (to get that error you must be creating a Measure) then reference that new column in the summarize

[user10]

Thanks Sean!

I was putting the column on my summarized table..  :smileyfrustrated

Your formula worked.  Thanks for the help!