Forum Discussion

jochendecraene's avatar
jochendecraene
Helper V
2 years ago
Solved

Has value based on id

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. ...
Interesting Links
Need Help
Show and Tell
  • Fowmy's avatar
    Fowmy
    2 years ago

    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's avatar
    Ritaf1983
    2 years ago

    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

jochendecraene's avatar
jochendecraene
Helper V
2 years ago

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?