Count a Row if an Entry Exists in Any Column

[Anonymous]

Hi,

I'm interested in counting the row if the term 'Motorist Assist' exists anywhere within several columns. So my total should be 23 say if I put this column in a Table visualization. Any suggestions?

Thanks in advance.

EventID	Resp1Type	Resp2Type	Resp3Type	Resp4Type
468734	Motorist Assist
468735	Motorist Assist
468736	Motorist Assist
468737	Motorist Assist
468738	Highway Patrol	Motorist Assist
468739	Motorist Assist
468740	Police	Fire	EMS
468741	Police
468742	Motorist Assist
468743	Motorist Assist
468744	Motorist Assist
468745	Motorist Assist
468746	Tow	Police	Fire	EMS
468747	Motorist Assist
468748	Motorist Assist
468749	Motorist Assist
468750	Motorist Assist
468751	Motorist Assist
468752	Motorist Assist
468753	Motorist Assist
468754	Motorist Assist
468755	Motorist Assist
468757	Tow	Police	Motorist Assist
468759	Police	Fire	EMS	Motorist Assist
468758	Motorist Assist
468760	DOT	Police	Motorist Assist	Motorist Assist

[AlB · Accepted solution]

Hi @Anonymous 

Create this measure and place it in a  card visual:

Measure =
VAR auxT_ =
    FILTER (
        Table1,
        VAR concat_ = Table1[Resp1Type] & Table1[Resp2Type] & Table1[Resp3Type] & Table1[Resp4Type]
        RETURN
            SEARCH ( "Motorist Assist", concat_,, 0 ) > 0
    )
RETURN
    COUNTROWS ( auxT_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the first column and select "Unpivot other columns".  Now write this measure

=calculate(countrows(data),data[value]="Motorist assist")

Hope this helps.

[jdbuchanan71 · Accepted solution]

@Anonymous 

One way to do it would be 

Measure =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Resp1Type] = "Motorist Assist"
            || 'Table'[Resp2Type] = "Motorist Assist"
            || 'Table'[Resp3Type] = "Motorist Assist"
            || 'Table'[Resp4Type] = "Motorist Assist"
    )
)

[AlB · Accepted solution]

Hi @Anonymous 

Create this measure and place it in a  card visual:

Measure =
VAR auxT_ =
    FILTER (
        Table1,
        VAR concat_ = Table1[Resp1Type] & Table1[Resp2Type] & Table1[Resp3Type] & Table1[Resp4Type]
        RETURN
            SEARCH ( "Motorist Assist", concat_,, 0 ) > 0
    )
RETURN
    COUNTROWS ( auxT_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers