Intersect, Split filter Bug?

[gekka]

Is a bug or my code is wrong?

On the picture above:

When i compared two worker in controlBox (Box 4 : Irwanto and RIki Hamdani) and the workers is not in the same row (Box 3) the result of worker chart (Box 5) is right, show one worker one bar.

Picture below:

But when i compared two worker and if that workers is in the same row the result of worker chart (Box 5) is also showing workers that i compared in controlBox (Box 4) together with coma (,).

How to make it only showing one worker for one bar?

@Steve , @TeeSee1 

[TeeSee1]

I do not know how you chart is created but the expression results in the first case and the second case will look different. (perhaps trying to count the number of appearances of the selected people either in [petugas foto] of [petugas video])

In the first case all the rows would have a single value list

In the second case you have a single value list and two lists of two values where both selected names appear in the [petugas foto] and [petugas video] and all of these are counted separately.

In order to what as I understand you want to accomplish, one way I can think of doing is to

1. create a VC in the people table and count the number of appearances in the filtered table
2. optionally create a slice based on the filter condition
3. create a chart view based 2.

[gekka]

AND(
IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Pekerja_ID],1)),ISNOTBLANK(
  INTERSECT(
    [Petugas Foto]+[Petugas Video],
    SPLIT(("" & ControlFilterTugasLiputan Slice[Pekerja_ID]), " , ")
  )
),true),


IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Dari Tanggal],1)),[Waktu Mulai]>=INDEX(ControlFilterTugasLiputan Slice[Dari Tanggal],1),true),

IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Sampai Tanggal],1)),[Waktu Mulai]<=INDEX(ControlFilterTugasLiputan Slice[Sampai Tanggal],1),true),

IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Dinas],1)),[Dinas]=INDEX(ControlFilterTugasLiputan Slice[Dinas],1),true),

IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Lokasi],1)),[Lokasi]=INDEX(ControlFilterTugasLiputan Slice[Lokasi],1),true),

IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Tema],1)),[Tema]=INDEX(ControlFilterTugasLiputan Slice[Tema],1),true),

IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Status Laporan Liputan],1)),[Status Laporan Liputan]=INDEX(ControlFilterTugasLiputan Slice[Status Laporan Liputan],1),true))

Here is my complete filter formula.

That expression will produce result this.

Because the names (Irwanto,Riki Hamdani) i search/filter  is in  one row the chart result is also showing the name i search in control chart bar also.

ive been trying SPLIT, UNIQUE, LIST, INTERSECT, etc with no success.

[TeeSee1]

If I am not mistaken, your chart groups by [pekerja_id] and count the aggregate number of rows.

So the total number of counts never exceeds the count of the selected/filtered rows.

If both of the values in your filter appear in FOTO and VIDEO, it has to be counted as either 'A' or 'B' or 'A,B' and cannot be counted as two rows belonging to A and B separately. And with the current app formula, it is counted as 'A,B' and appears as a separate bar.

That is why I proposed what I proposed in my first response.

[Marc_Dillon]

Does changing the SPLIT() delimiter to " , " (space-comma-space) make it work?

If not, try combining that with using TEXT() to convert the table[column] result into text, instead of concating a blank string in front. i.e. SPLIT(TEXT(table[column]) , " , " )

[gekka]

It is the same. All code modification stuck.

[gekka]

Finaly, i was able solve yhe problem.

Based on TeeSee1 proposed solution.

COUNT(
SELECT(FilteredLaporanTugasLiputan Slice[Laporan_ID], or([Petugas Foto] = [_THISROW].[Nama], [Petugas Video] = [_THISROW].[Nama]))
)

But it produce another problem. Il post it later. 

What a great community.