How can I create a new column based on the condition using group by column.

[Anonymous]

I have a table in which I need to check the condition if 0 appears for a country in the "dq_status_copy" column then the new column also has 0 for the specific country. If all the value inside the "dq_status_copy" is 1 then It will show 1. DQ Test is the new column.

Here you can see, For BE there is 0 inside the "dq_status_copy" for country BE then I need 0 in all rows for BE inside the "DQ Test" for AT we have all 1 then we need 1 in all Rows for AT. Kindly help me to resolve the issue.

Ex:-

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution, create a calculated column.

DQ Test =
IF (
    COUNTROWS (
        FILTER (
            'DQ Log',
            'DQ Log'[country_code] = EARLIER ( 'DQ Log'[country_code] )
                && 'DQ Log'[dq_status_copy] = 0
        )
    ) > 0,
    0,
    1
)

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @bilalrana ,

I tried this solution but seems like didn't work. Please refer to the snip below.

[bilalrana]

Hi @Anonymous 

To create the column, follow the following steps:

Go to Data Table on the left of the screen and select the respective table to add new column.

Now, write the DAX script and click on the Tick button.

DQ Test =

var CountryEntryCount = CALCULATE (
COUNT ( Table1[country_code] ),
ALLEXCEPT ( Table1, Table1[country_code] )
)

var SuccessCount = CALCULATE (
SUM ( Table1[dq_status_copy] ),
ALLEXCEPT ( Table1, Table1[country_code] )
)

var result = if(CountryEntryCount = SuccessCount, 1, 0)

return result

I have tested the solution, Kindly like the reply and submit as a solution.