cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
praveenpasila
Advocate I
Advocate I

Assign value based on condition

Hi All,

 

I am trying to achieve a scenario to assign same flag based on condition.

 

Assume Company A has 3 different ID's and if the flag is Y for any one ID then we assign Y to all ID's in company A

If Flag is N then assign N for all id's for that company Ex--B.

Just a note Company is coming from one table and ID and Flag are coming from another table in the data model

 

 

CompanyIDFlag
A1Y
A2N
A3N
A4 
B5N
B6N
B7N
C8 

Desired O/P:

CompanyIDFlagMeasure
A1YY
A2NY
A3NY
A4 Y
B5NN
B6NN
B7NN
C8  
 
Tried Measure = IF(count('Table'[ID]) >=1 && MAX('Table'[Flag]) = "Y","Y","N")
 
Can some one please suggest.
Thank you

 

5 REPLIES 5
Samarth_18
Super User
Super User

Hi @praveenpasila ,

 

You can create a column like this:-

Column =
VAR flag_count =
    COUNTROWS (
        FILTER (
            'Table (2)',
            'Table (2)'[Company] = EARLIER ( 'Table (2)'[Company] )
                && 'Table (2)'[Flag] <> BLANK ()
        )
    ) + 0
VAR result =
    COUNTROWS (
        FILTER (
            'Table (2)',
            'Table (2)'[Company] = EARLIER ( 'Table (2)'[Company] )
                && 'Table (2)'[Flag] = "Y"
        )
    )
RETURN
    IF ( flag_count = 0, BLANK (), IF ( result > 0, "Y", "N" ) )

 

Output:-

Samarth_18_0-1653056551000.png

 

BR,

Samarth


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


Hi Samarth,

 

Thank you for your quick responce.

The output is as expected but as mentioned in my earlier post the columns are coming from differnt tables in the data model.When trying to add flag column its not allowing me.

Data model looks something like below

praveenpasila_0-1653224191380.png

 

Tried below column

Column =
VAR flag_count =
COUNTROWS (
FILTER (
'Table',
'Table'[Company] = EARLIER ( 'Table'[Company] )
&& 'Table (3)'[Flag] <> BLANK ()
)
) + 0
VAR result =
COUNTROWS (
FILTER (
'Table',
'Table'[Company] = EARLIER ( 'Table'[Company] )
&& 'Table (3)'[Flag] = "Y"
)
)
RETURN
IF ( flag_count = 0, BLANK (), IF ( result > 0, "Y", "N" ) )

 

Sample data:

CompanyID-A
A123
B456
C789

 

ID-SID-A
1123
2123
3123
4123
4456
5456
6456
8789

 

ID-Sflag
1Y
2N
3N
4 
4N
5N
6N
8 

 

Please suggest if we can make any changes

Thank you

Hi @praveenpasila,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? We can test to coding formula on it and create a sample formula to share.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft ,

 

I was not able to publish the pbix file and I have provided sample data in the earlier post.

 

Sample data:

CompanyID-A
A123
B456
C789

 

ID-SID-A
1123
2123
3123
4123
4456
5456
6456
8789

 

ID-Sflag
1Y
2N
3N
4 
4N
5N
6N
8 

 

 

I tried lookup and related functions but its not working when I want the new column in Table 1

 

praveenpasila_0-1653913582597.png

Data model is as above and the reason I want the flag in table1 is its a dimension table and Table 2 is a fact table in real time scenario.

 

I was able to create Flag in Table2 with below formulas

New Comapany = RELATED('Table'[Company])
New Flag = var joincol = 'Table (2)'[ID-S]
var Newcol = CALCULATE(MAX('Table (3)'[flag]),'Table (3)'[ID-S] = joincol)
return Newcol
 
Flag Col =
VAR flag_count =
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Comapany] = EARLIER ( 'Table (2)'[Comapany])
&& 'Table (2)'[Flag] <> BLANK ()
)
) + 0
VAR result =
COUNTROWS (
FILTER (
'Table (2)',
'Table (2)'[Comapany] = EARLIER ( 'Table (2)'[Comapany] )
&& 'Table (2)'[Flag] = "Y"
)
)
RETURN
IF ( flag_count = 0, BLANK (), IF ( result > 0, "Y", "N" ) )

 

O/P

praveenpasila_1-1653913770491.png

Please suggest if I am missing something to make it work.

 

HI @praveenpasila,

It seems like the expression can't map correctly based on the 'Many to Many' relationships.
I'd like to suggest you create a new table as a bridge to link two tables, then you can add a calculated column on the second table to show the flags:

Bridge =
DISTINCT ( UNION ( ALL ( Mapping[ID-S] ), ALL ( Flag[ID-S] ) ) )

2.png

Tag =
VAR result =
    COUNTROWS (
        FILTER ( Flag, [ID-S] = EARLIER ( Mapping[ID-S] ) && Flag[flag] <> BLANK () )
    )
VAR result2 =
    COUNTROWS (
        FILTER ( Flag, [ID-S] = EARLIER ( Mapping[ID-S] ) && Flag[flag] = "Y" )
    )
RETURN
    IF ( result > 0, IF ( result2 > 0, "Y", "N" ) )

1.png
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors