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

Ritaf1983's avatar
Ritaf1983
Super User
2 years ago

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's avatar
    jochendecraene
    Helper V
    2 years ago

    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's avatar
      Ritaf1983
      Super User
      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