Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
user10
Frequent Visitor

Set column value based on a set of records in table

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.

 

 

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

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! Smiley Happy

View solution in original post

4 REPLIES 4
Sean
Community Champion
Community Champion

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! Smiley Happy

user10
Frequent Visitor

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
Community Champion
Community Champion

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

Thanks Sean!

 

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

Your formula worked.  Thanks for the help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.