Has value based on id

[jochendecraene]

Hi

I got a list of caseid's and type of contacts with clients. I need to make the difference between id's where there has been a face to fase contact and the ones without a face to face contact.

I added a column in power quer with a simple if statement

Now I need to write a dax measure to get the number caseid's with and without a face to face contact.

Can someone help me out, point me in the right direction, show me an interesting article on these kind of scenario's?

thnx!

[Fowmy · Accepted solution]

@jochendecraene 

The the following two measures:

With_F2F =  
	CALCULATE(
		DISTINCTCOUNT( Table02[caseid] ),
		Table02[Face to face] = "YES"
	)

No_F2F =  
	SUMX(
		DISTINCT( Table02[caseid] ),
		CALCULATE( INT(NOT "YES" IN VALUES( Table02[Face to face] )))
	)

[jochendecraene]

@Ritaf1983 @Fowmy  thank you both very much! Both solutions are good!

I found another solution using power query, by combining all the values per id in 1 colom, add an if statement and than simply calculate bases on the outcome of the if statement.

Is this a good alternative approach?

Next questions a need to anwser are:

- how much types of contacts are there per id

- wich combinations of contacts and how many are there in the whole dataset

For the first question I would simply count the words per id

The second one for me is more difficult. I would have to take a step back and remove all doubles and then write if statement for the possible cominations 

Are there any dax solutions for these 2 questions based on the orginal table?

[Ritaf1983]

Hi @jochendecraene 
If you need to analyze data row by row, combining rows as you did, is not a good practice.
About the combinations, it seems like a logic of bucket analysis, please check these links:

https://blog.finance-bi.com/power-bi-basket-analysis/

https://www.daxpatterns.com/basket-analysis/

https://www.youtube.com/watch?v=yzDGvZEtYdQ

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[jochendecraene]

@Ritaf1983 thank you for your feedback and the links. This is helpfull!

[Ritaf1983]

Happy to help 🙂

[Fowmy · Accepted solution]

@jochendecraene 

The the following two measures:

With_F2F =  
	CALCULATE(
		DISTINCTCOUNT( Table02[caseid] ),
		Table02[Face to face] = "YES"
	)

No_F2F =  
	SUMX(
		DISTINCT( Table02[caseid] ),
		CALCULATE( INT(NOT "YES" IN VALUES( Table02[Face to face] )))
	)

[Ritaf1983]

Hi @jochendecraene 
You can achieve the desired result even without adding a column.
You can use 3 measures :

1 Face_to_face = calculate(distinctcount('yourtable'[caseid]),'yourtable'[type]="face to face")

2. others_=calculate(distinctcount('yourtable'[caseid]),'yourtable'[type]<>"face to face")

3 Diff = [ Face_to_face]-[others_]

more information about "calculate" function is here:

https://learn.microsoft.com/en-us/dax/calculate-function-dax

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[jochendecraene]

hi @Ritaf1983 

thnx, but that doesn't give me the result I need. Maybe I wasn't clear.

What I need is the number cases where there has never been a face to face contact and the ones where there has been one.

So the result shoud be:

cases with face to face contact: 5

cases without face to face contact: 2

[Ritaf1983 · Accepted solution]

Ok @jochendecraene 
This is a little tricky.

The measures you can use :

1.

Count_face_to_face =

COUNTX(

    CALCULATETABLE('Table','Table'[Type]="face to face"),DISTINCTCOUNT('Table'[Id]))

2. 

Count_others =

VAR t =

    SUMMARIZE('Table','Table'[Id],"face_to_face_count",[Count_face_to_face])

    RETURN

    CALCULATE(DISTINCTCOUNT('Table'[Id]),FILTER(t,ISBLANK([face_to_face_count])))

Result :

Pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly