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
Anonymous
Not applicable

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

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:-

anupammishra_2-1660733436076.png

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1661161698679.png

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.

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

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.

vkalyjmsft_0-1661161698679.png

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
Not applicable

Thanks @bilalrana ,

 

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

anupammishra_0-1660888748026.png

 

bilalrana
Resolver I
Resolver I

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.

bilalrana_1-1660744517612.png

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

bilalrana_2-1660744597076.png

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.




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.

Top Solution Authors
Top Kudoed Authors