Re: Equivalent to expression arithmetic "+"

gekka · 01-31-2023 08:25 AM

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

The code above is for dashboard filtering and working fine because both [Petugas Foto] and [Petugas Video] have Type Data Column = Enum.

Recently i had to change both [Petugas Foto] and [Petugas Video] type Column to "Name" to make other part of my code to work that more prioritized.

The problem is when i change [Petugas Foto] and [Petugas Video] to 'Name" type data column it is produce no error but give me blank result when perform filtering.

But when the [Petugas Foto] and [Petugas Video] column change to Enum type data column is give error "Cannot compare List with Name"

So, is there any Equivalent of expression arithmetic "+" that work with name Type Data Column? or any workaround to compare list with Name date type?

SkrOYC

Seems like your expression was not complete. Can you share it again?

gekka

Here is my dashboard filter expression.

AND(
IF(ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Pekerja_ID],1)),ISNOTBLANK(
  INTERSECT(Laporan Tugas Liputan[Petugas Foto]+Laporan Tugas Liputan[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))

and this one my prioritized expression. It count how many the number of appearances in the filtered table.

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

SkrOYC

@gekka wrote:

So, is there any Equivalent of expression arithmetic "+" that work with name Type Data Column? or any workaround to compare list with Name date type?

You are producing a List of Names in this case so + shouldn't stop working at all.

AND(
  IF(
    ISNOTBLANK(INDEX(ControlFilterTugasLiputan Slice[Pekerja_ID],1)),
    ISNOTBLANK(
      INTERSECT(
        Laporan Tugas Liputan[Petugas Foto]+Laporan Tugas Liputan[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
  )
)

In case someone want to take a look

Steve

Instead of this:

Laporan Tugas Liputan[Petugas Foto]+Laporan Tugas Liputan[Petugas Video]

Try this:

LIST()+Laporan Tugas Liputan[Petugas Foto]+Laporan Tugas Liputan[Petugas Video]

Steve

Just for fun, her's how I would reformat your expression to my own preferences:

AND(
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Pekerja_ID])),
    ISNOTBLANK(
      INTERSECT(
        LIST()+Laporan Tugas Liputan[Petugas Foto]+Laporan Tugas Liputan[Petugas Video],
        SPLIT(("" & ControlFilterTugasLiputan Slice[Pekerja_ID]), " , ")
      )
    )
  ),
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Dari Tanggal])),
    [Waktu Mulai]>=ANY(ControlFilterTugasLiputan Slice[Dari Tanggal])
  ),
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Sampai Tanggal])),
    [Waktu Mulai]<=ANY(ControlFilterTugasLiputan Slice[Sampai Tanggal])
  ),
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Dinas])),
    [Dinas]=ANY(ControlFilterTugasLiputan Slice[Dinas])
  ),
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Lokasi])),
    [Lokasi]=ANY(ControlFilterTugasLiputan Slice[Lokasi])
  ),
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Tema])),
    [Tema]=ANY(ControlFilterTugasLiputan Slice[Tema])
  ),
  OR(
    ISBLANK(ANY(ControlFilterTugasLiputan Slice[Status Laporan Liputan])),
    [Status Laporan Liputan]=ANY(ControlFilterTugasLiputan Slice[Status Laporan Liputan])
  )
)

SkrOYC

@Steve wrote:

Just for fun, her's how I would reformat your expression to my own preferences

I also like that game 😅, although I've found ANY(X) being problematic while INDEX(X, 1) never fails.

Aaaand I'm clearly and indentation nerd