Help with counting all rows as long as it includes one criteria

[paguy215]

I have a case where I am trying to identify all customer call tyeps provided they include one specific type.   So I have a field with customer name and a field with all customer call types.  A call type for example could be Sales, Repair, Billing, etc.    I want to be able to get a list of customers that had other interactions as a result of a Billing interaction.   So Customer A might have 3 rows...one for Billing, one for Repair, one for Sales since they had 3 different calls.   But I want to be able to include only customers that had at least one Billing calls but also pull in their other calls.   So I can't filter on Call Type as Billing because I would exclude the others.

Ideally if I can incorporate the date field I have to see if these were all on the same day, even better.   

Any help is greatly appreciated 

[tamerj1 · Accepted solution]

Hi @paguy215 
You can create a disconnected table that contains the list of Call Types to use it as a slicer selection

CallType =
ALLNOBLANKROW ( 'Table'[CallType] )

Make sure no realtionship is automatically created, delete it if that happens.

Then place the following measure in the filter pane of the table visual, select "is not blank" then apply the filter

FilterMeasure =
COUNTROWS (
    FILTER (
        VALUES ( 'Table'[Customer] ),
        ISEMPTY (
            EXCEPT (
                VALUES ( CallType[CallType] ),
                CALCULATETABLE ( VALUES ( 'Table'[CallType] ) )
            )
        )
    )
)

Only the custermrs that made all the selected CallTypes will be visible and along with all there Calltypes.

[paguy215]

@tamerj1  Thanks this works...out of curiosity do you know if its possible to go one level further and show only interactions on the same date?   So the billing calls and then other calls that happened on that same date... I have a standard date field, but there might be cases where they have a billing call one day and then a repair call a week later

[tamerj1]

@paguy215 

That might be very slow but worth trying 

FilterMeasure =
COUNTROWS (
FILTER (
VALUES ( 'Table'[Customer] ),
VAR SelectedCallTypes =
VALUES ( CallType[CallType] )
VAR Dates =
CALCULATETABLE (
VALUES ( 'Table'[Date] ),
TREATAS ( SelectedCallTypes, 'Table'[CallType] )
)
VAR CurrentCallTypes =
CALCULATETABLE (
VALUES ( 'Table'[CallType] ),
TREATAS ( Dates, 'Table'[Date] )
)
RETURN
ISEMPTY ( EXCEPT ( SelectedCallTypes, CurrentCallTypes ) )
)
)

[paguy215]

i'll give it a whirl, thanks for your help!

[paguy215]

Hi. @tamerj1 ..thanks for the answer!   So I can't quite get this to work.  I tried what you said...the new table is disconnected and I added in that filter measure and selected 'is not blank'...however when I choose 'Billing' from the Call Type in the original query, I get all blank  .... In fact its blank with all interactions selected

attached is a screenshot, in this case 'interactions by source' is the main table, InteractionFilter is the disconnected one I created per your formula, and Reason 2 is the call type

is there something else I missed??

[paguy215]

NM I think I got it, adding the interaction filter from the new table did the trick...thanks!