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.

 

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!

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

8 Replies

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

  • 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?