Distinct count of column value from a table based on measure in a another table

[Anonymous]

This is a followup to previous post:  Solved: Re: Help with grouped sum - Microsoft Power BI Community

I have these two tables A and B.   

All ConsumerIdtaskcodes in Table A are also in Table B.

Links to CSV A and B:

https://drive.google.com/file/d/1-PquFaIlYsS_qeY-D1D1uiwXl2NZSUH8/view?usp=sharing

https://drive.google.com/file/d/1_pOz3eAUAxr5-tAdXAeZxrXwPbRLVQOE/view?usp=sharing

There are some consumerIdtaskcode in B but not in A.

Table B has confidencescores column but table A does not.

The fisrt task is to create a measure to count the ConsumerIdTaskcode in table B with confidence score (aggregated per ConsumeridTaskcode) above a threshhold (say 71).  The following measure works for that:   

Count Over 71 =
COUNTROWS (
    FILTER (
        VALUES ( 'report ConsumerTaskCodes'[ConsumerIdTaskcode] ),
        CALCULATE ( SUM ( 'report ConsumerTaskCodes'[confidenceScore] ) ) > 71
    )
)

Table B result

Question 1:

Here is my next task:

Identify and create a measure to determine ONLY distinct count of ConsumerIdtaskcodes in Table A that are also in B with confidencescore  (in B) above 71.

Answer should be 2:  From Table A

Table A result

Question 2.  By extension, create a dax calculation to  

List the codes that are in Table B but not in A (still with Confidencescore > 71.

Links to CSV A and B:

https://drive.google.com/file/d/1-PquFaIlYsS_qeY-D1D1uiwXl2NZSUH8/view?usp=sharing

https://drive.google.com/file/d/1_pOz3eAUAxr5-tAdXAeZxrXwPbRLVQOE/view?usp=sharing

@mahoneypat 

[Greg_Deckler · Accepted solution]

@Anonymous Question 1:

Count Over 71 Distinct = 
VAR __TableA = DISTINCT('dataTableA'[ConsumerIdTaskcode])
VAR __TableB =
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'report ConsumerTaskCodes',
                [ConsumerIdTaskcode],
                "__Confidence",SUM('report ConsumerTaskCodes'[confidenceScore])
            ),
            [__Confidence] > 71
        ),
        "ConsumerIdTaskcode",[ConsumerIdTaskcode]
    )
RETURN
    COUNTROWS(INTERSECT(__TableA,__TableB))

Question 2:

Count Over 71 Distinct Reverse = 
VAR __TableA = DISTINCT('dataTableA'[ConsumerIdTaskcode])
VAR __TableB =
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'report ConsumerTaskCodes',
                [ConsumerIdTaskcode],
                "__Confidence",SUM('report ConsumerTaskCodes'[confidenceScore])
            ),
            [__Confidence] > 71
        ),
        "ConsumerIdTaskcode",[ConsumerIdTaskcode]
    )
RETURN
    COUNTROWS(EXCEPT(__TableB,__TableA))

[ryan_mayu]

@Anonymous 

you can try this

Measure = 
VAR tbl=SUMMARIZE('dataTableB',dataTableB[ConsumerIdTaskcode],"sum",sum(dataTableB[confidenceScore]))
return COUNTROWS(FILTER(tbl,[sum]>71))

Measure 2 = 
VAR tbl=ADDCOLUMNS(SUMMARIZE('dataTableB',dataTableB[ConsumerIdTaskcode],"sum",sum(dataTableB[confidenceScore])),"check",if(maxx(FILTER(dataTableA,dataTableA[ConsumerIdTaskcode]=dataTableB[ConsumerIdTaskcode]),dataTableA[ConsumerIdTaskcode])<>"","Yes"))
return COUNTROWS(FILTER(tbl,[sum]>71&&[check]="Yes"))

[Greg_Deckler · Accepted solution]

@Anonymous Question 1:

Count Over 71 Distinct = 
VAR __TableA = DISTINCT('dataTableA'[ConsumerIdTaskcode])
VAR __TableB =
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'report ConsumerTaskCodes',
                [ConsumerIdTaskcode],
                "__Confidence",SUM('report ConsumerTaskCodes'[confidenceScore])
            ),
            [__Confidence] > 71
        ),
        "ConsumerIdTaskcode",[ConsumerIdTaskcode]
    )
RETURN
    COUNTROWS(INTERSECT(__TableA,__TableB))

Question 2:

Count Over 71 Distinct Reverse = 
VAR __TableA = DISTINCT('dataTableA'[ConsumerIdTaskcode])
VAR __TableB =
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'report ConsumerTaskCodes',
                [ConsumerIdTaskcode],
                "__Confidence",SUM('report ConsumerTaskCodes'[confidenceScore])
            ),
            [__Confidence] > 71
        ),
        "ConsumerIdTaskcode",[ConsumerIdTaskcode]
    )
RETURN
    COUNTROWS(EXCEPT(__TableB,__TableA))

[Anonymous]

@Greg_Deckler 

In Question 2,  how do I determine the actual Taskcodes (not ConsumerIdTaskcode ) that are in Table B but not in A (still with Confidencescore > 71?   This is where I was heading and thougt I could figure it out, but proving difficult.   Thanks.  I think first dax is fine. 

[Greg_Deckler]

@Anonymous Just use CONCATENATEX instead of COUNTROWS.

[Anonymous]

@Greg_Deckler 

The first part of ConsumerIdTaskcode is a whole number (variable lenght) while the other part is a text.  E.g. for ConsumerIdTaskcode 1961L160,  the Taskcode is  L160.    So I need to pick the text out of ConsumerIdTaskcode to represent the Taskcode.    

[Greg_Deckler]

@Anonymous Use CONCATENATEX(EXCEPT(__TableB,__TableA), RIGHT([ConsumerIdTaskcode,4),", ") ?

[Anonymous]

Thank you again, @Greg_Deckler , can we report this based on the column Taskcode instead ?   In real life data,  the Taskcode part of ConsumerIdTaskcode is not always 4 characters - very much varies.  So also does the ConsumerId.   So it is safer to report the column Taskcode standalone. 

[Greg_Deckler · Accepted solution]

@Anonymous Try:

Count Over 71 Distinct Reverse = 
VAR __TableA = DISTINCT('dataTableA'[ConsumerIdTaskcode])
VAR __TableB =
    SELECTCOLUMNS(
        FILTER(
            SUMMARIZE(
                'report ConsumerTaskCodes',
                [ConsumerIdTaskcode],
                "__Confidence",SUM('report ConsumerTaskCodes'[confidenceScore])
            ),
            [__Confidence] > 71
        ),
        "ConsumerIdTaskcode",[ConsumerIdTaskcode]
    )
  VAR __Ids = EXCEPT(__TableB,__TableA)
  VAR __Table = FILTER('report ConsumerTaskCodes',[ConsumerIdTaskcode] IN __Ids)
RETURN
  CONCATENATEX(__Table,[taskCode],",")

[Anonymous]

You are awesome!

I did some spot checks and it is looking good so far.

Can it be made a distinct list and ordered in a column?

@Greg_Deckler 

[Greg_Deckler]

@Anonymous Well, you can't return a table in a measure. It would have to be a calculated table. I feel like I am working with like half of the information about what you are trying to accomplish.