Using DAX to Create a New Column Based on Groups Within Table

[JanCronje16]

Hello,

The left 7 colums are part of a larger table that I must use to get the desiredd result in the require result colum for every row in my table.

I need to ignore any blank fields and if the remaing fields all contain the same datareturn that value in the results column. If the remaining fields do not contain the same data the result should be "Mixed".

Any help will be appriciated.

Thanks

Jan

[danextian · Accepted solution]

Hi @JanCronje16 

Try this:

Output =
// Define a variable `_TBL` to create a virtual table with specified columns
VAR _TBL = {
    'Table'[Column1],
    'Table'[Column2],
    'Table'[Column3],
    'Table'[Column4],
    'Table'[Column5]
} // Calculate the count of unique non-blank values in `_TBL`
VAR UniqueCount =
    COUNTROWS (
        // Count the number of rows in the resulting table
        DISTINCT (
            // Extract unique values from the filtered table
            FILTER (
                // Filter out blank values from `_TBL`
                _TBL,
                NOT ( ISBLANK ( [Value] ) ) // Keep rows where [Value] is not blank
            )
        )
    ) // Return the result based on the count of unique values
RETURN
    IF (
        UniqueCount > 1,
        // If there is more than 1 unique non-blank value
        "Mixed",
        // Return "Mixed"
        MAXX ( _TBL, [Value] ) // Otherwise, return the maximum value in `_TBL`
    )

[shafiz_p · Accepted solution]

Hi @JanCronje16 , Try this:

Result = 
VAR NonBlankValues = 
    UNION(
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column1])) && 'Table'[Column1] = EARLIER('Table'[Column1])), "Value", 'Table'[Column1]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column2])) && 'Table'[Column2] = EARLIER('Table'[Column2])), "Value", 'Table'[Column2]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column3])) && 'Table'[Column3] = EARLIER('Table'[Column3])), "Value", 'Table'[Column3]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column4])) && 'Table'[Column4] = EARLIER('Table'[Column4])), "Value", 'Table'[Column4]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column5])) && 'Table'[Column5] = EARLIER('Table'[Column5])), "Value", 'Table'[Column5]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column6])) && 'Table'[Column6] = EARLIER('Table'[Column6])), "Value", 'Table'[Column6]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column7])) && 'Table'[Column7] = EARLIER('Table'[Column7])), "Value", 'Table'[Column7])
    )
VAR DistinctNonBlankValues = 
    FILTER(
        DISTINCT(NonBlankValues),
        [Value] <> BLANK()
    )
VAR FirstValue = MAXX(DistinctNonBlankValues, [Value])
VAR AllSame = 
    COUNTROWS(
        FILTER(
            DistinctNonBlankValues,
            [Value] <> FirstValue
        )
    ) = 0

RETURN
    IF(
        COUNTROWS(DistinctNonBlankValues) = 0,
        BLANK(),
        IF(
            AllSame,
            FirstValue,
            "Mixed"
        )
    )

Change column names accordingly.

Outputs:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[shafiz_p · Accepted solution]

Hi @JanCronje16 , Try this:

Result = 
VAR NonBlankValues = 
    UNION(
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column1])) && 'Table'[Column1] = EARLIER('Table'[Column1])), "Value", 'Table'[Column1]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column2])) && 'Table'[Column2] = EARLIER('Table'[Column2])), "Value", 'Table'[Column2]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column3])) && 'Table'[Column3] = EARLIER('Table'[Column3])), "Value", 'Table'[Column3]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column4])) && 'Table'[Column4] = EARLIER('Table'[Column4])), "Value", 'Table'[Column4]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column5])) && 'Table'[Column5] = EARLIER('Table'[Column5])), "Value", 'Table'[Column5]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column6])) && 'Table'[Column6] = EARLIER('Table'[Column6])), "Value", 'Table'[Column6]),
        SELECTCOLUMNS(FILTER(ALL('Table'), NOT(ISBLANK('Table'[Column7])) && 'Table'[Column7] = EARLIER('Table'[Column7])), "Value", 'Table'[Column7])
    )
VAR DistinctNonBlankValues = 
    FILTER(
        DISTINCT(NonBlankValues),
        [Value] <> BLANK()
    )
VAR FirstValue = MAXX(DistinctNonBlankValues, [Value])
VAR AllSame = 
    COUNTROWS(
        FILTER(
            DistinctNonBlankValues,
            [Value] <> FirstValue
        )
    ) = 0

RETURN
    IF(
        COUNTROWS(DistinctNonBlankValues) = 0,
        BLANK(),
        IF(
            AllSame,
            FirstValue,
            "Mixed"
        )
    )

Change column names accordingly.

Outputs:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[danextian · Accepted solution]

Hi @JanCronje16 

Try this:

Output =
// Define a variable `_TBL` to create a virtual table with specified columns
VAR _TBL = {
    'Table'[Column1],
    'Table'[Column2],
    'Table'[Column3],
    'Table'[Column4],
    'Table'[Column5]
} // Calculate the count of unique non-blank values in `_TBL`
VAR UniqueCount =
    COUNTROWS (
        // Count the number of rows in the resulting table
        DISTINCT (
            // Extract unique values from the filtered table
            FILTER (
                // Filter out blank values from `_TBL`
                _TBL,
                NOT ( ISBLANK ( [Value] ) ) // Keep rows where [Value] is not blank
            )
        )
    ) // Return the result based on the count of unique values
RETURN
    IF (
        UniqueCount > 1,
        // If there is more than 1 unique non-blank value
        "Mixed",
        // Return "Mixed"
        MAXX ( _TBL, [Value] ) // Otherwise, return the maximum value in `_TBL`
    )

[JanCronje16 · Accepted solution]

Thanks, works great.