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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors