cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BartStruyf
Regular Visitor

calculated colum to see if a an account has contacts with a certain role or not

Hello,

I'm struggeling to get this answer. 
My customers have mulptiple contacts, with different roles.  I want to summurize in a column if a customer has a contact with a certain rol or not.  
I have one table with :

 
Account IDRolHAS A?
1AY
1BY
2AY
2CY
3BN
3BN
 

I don't know how to write the right DAX-formula.

Thansk for you help,

Bart

4 REPLIES 4
Anonymous
Not applicable

// T is your table.

[Has A?] = // calc column
var __account = T[Account]
var __hasA =
	NOT ISEMPTY(
		FILTER(
			T,
			T[Account] = __account
			&&
			T[IDRol] = "A"
		)
	)
RETURN
	if( __hasA, "Y", "N" )

 

Best

D

mahoneypat
Microsoft
Microsoft

I agree it is better to right a measure when you can.  But also here is an expression for your calculated column.

 

HasA = if(calculate(Countrows(YourTable), All(YourTable), YourTable[Rol]="A", YourTable[Account ID]=Earlier(YourTable[Account ID]))>0, "Y", "N")

 

If this solution works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


razmochaev
Helper I
Helper I

Hi @BartStruyf,

First of all, I would not recommend to use calculated column for this task. A measure would be more appropriate.

 

But to solve your problem you might do the following.

If you don't have separate dimension table for Roles you need to:

1. Create a measure [Num of unique Roles]: = DISTINCTCOUNT('YourTable'[IDRol])

2. Create a calculated column: = CALCULATE([Num of unique Roles];filter(VALUES('YourTable'[IDRol]);'YourTable'[IDRol]="A"))>0

Anonymous
Not applicable

Hi @razmochaev

And what if he wants to be able to slice by these values? Would you recommend a measure in this case as well?

Best
D

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

Power BI Dev Camp Session 25 with aka link 768x460.jpg

Check it Out!

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Top Solution Authors
Top Kudoed Authors