topic Re: Help with counting all rows as long as it includes one criteria in DAX Commands and Tips

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

Anonymous — Mon, 22 Apr 2024 13:51:30 GMT

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

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

tamerj1 — Mon, 22 Apr 2024 14:18:28 GMT

Hi Anonymous
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.

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

Anonymous — Mon, 22 Apr 2024 14:31:57 GMT

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

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

Anonymous — Mon, 22 Apr 2024 15:08:41 GMT

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

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

Anonymous — Mon, 22 Apr 2024 15:21:31 GMT

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

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

tamerj1 — Mon, 22 Apr 2024 17:13:55 GMT

Anonymous

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 ) )
)
)

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

Anonymous — Mon, 22 Apr 2024 18:18:16 GMT

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