cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Need help on calculated column

 Project Code Flag 1 A 1 1 B 1 1 C 1 2 B 1 2 C 1 3 D 0 3 F 0 3 Q 0 4 B 1 5 A 0 6 C 0

I want to create a caculated column Flag, If project contains Code B then all records in project will be flgged as 1.  By the way, If the requirment is measure,how to create?

1 ACCEPTED SOLUTION
Super User

@EricWu Maybe:

``````Column =
VAR __Project = [Project]
VAR __Codes = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Project]=__Project),"Code",[Code]))
RETURN
IF("B" IN __Codes,1,0)

Measure =
VAR __Project = MAX([Project])
VAR __Codes = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Project]=__Project),"Code",[Code]))
RETURN
IF("B" IN __Codes,1,0)
``````

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

3 REPLIES 3
Super User

Hi,

This calculated column works as well

``=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Project]=EARLIER(Data[Project])&&Data[Code]="B"))>0,1,0)``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

@EricWu Maybe:

``````Column =
VAR __Project = [Project]
VAR __Codes = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Project]=__Project),"Code",[Code]))
RETURN
IF("B" IN __Codes,1,0)

Measure =
VAR __Project = MAX([Project])
VAR __Codes = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[Project]=__Project),"Code",[Code]))
RETURN
IF("B" IN __Codes,1,0)
``````

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Frequent Visitor

@Greg_Deckler  Thanks a lot, it worked perfertly

## Helpful resources

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Check it Out!

Click here to read more about the November 2021 Updates!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors