Exclude values in a straight table based on expression

[pgloc2020]

Hi All,

I have a straight table with two columns. First is dimension "Type of Work" and second is a measure "Total Authors". My user story is - "I want to display only those  "Type of Work" where authors are 2 or more. 

I used the following expression in the measure field to get the authors counts -

=count(distinct aggr(if(count([Application_ID])>1,[Application_ID]),[Application_ID]))

I am getting the desired result, but the issue is I am also getting the "Nature of work" where there are only 1 author and in the author_count column, it is showing 0 as shown below-

I tried following expressions in the dimension also but cannot eliminate records where author counts are less than 2

=Aggr(If(Concat( [Application_ID]) > 1, [NATURE_OF_AUTHORSHIP]), [NATURE_OF_AUTHORSHIP])

=Aggr(DISTINCT If(( Count([Application_ID]) > 1) ,[NATURE_OF_AUTHORSHIP]),[NATURE_OF_AUTHORSHIP])

=count(count({$<[Application_ID]>1>} [NATURE_OF_AUTHORSHIP])

=If(Len(Trim([Application_ID])) > 1, NATURE_OF_AUTHORSHIP)

My data has following columns-

Please help me what expression should I use to exclude records  where authors are less then 2.

[BrunPierre]

Try this expression

Count({<NATURE_OF_AUTHORSHIP={"=Count(APP_ID)>1"}>}DISTINCT APP_ID)

[pgloc2020]

Hi, I tried this expression

Count({<[NATURE_OF_AUTHORSHIP]={"=Count(APP_ID)>1"}>}DISTINCT APP_ID)

and it says invalid

[BrunPierre]

You should provide a sample of data as well as the expected outcome.

[pgloc2020]

My data looks like this -

NAME	TYPE	SOURCE	APP_ID	NATURE_OF_AUTHORSHIP
Q	AUTHORS	APPL	1-171	Fabric, Textile, or Wallpaper Designs
Jaipur	AUTHORS	APPL	1-299	Fabric, Textile, or Wallpaper Designs
Beth	AUTHORS	APPL	1-981	Jewelry Designs
Brent	AUTHORS	APPL	1-401	Pictorial or Graphic Works
Jaipur Living	AUTHORS	APPL	1-491	Fabric, Textile, or Wallpaper Designs
michael	AUTHORS	APPL	1-291	Pictorial or Graphic Works
michael	AUTHORS	APPL	1-291	Pictorial or Graphic Works
Fiona	AUTHORS	APPL	1-521	Pictorial or Graphic Works
PHEDRA	AUTHORS	APPL	1-521	Pictorial or Graphic Works
PHEDRA	AUTHORS	APPL	1-521	Pictorial or Graphic Works

In above data, some App_ID has 2 authors, for example - App_ ID 1-291 has listed 2 authors. and App_ID 1-521 has 3 authors listed.

In my desired result, I want to create a straight table chart where only those nature of work listed where author counts are 2 0r more. I want to ignore app id's count where there are only 1 author is listed. In above example, I just want to see following result- 

NATURE_OF_AUTHORSHIP	App_IDs	Author Counts
Pictorial or Graphic Works	1	2
Pictorial or Graphic Works	1	3

[BrunPierre]

=Count(Aggr(NODISTINCT If(Count({$<NATURE_OF_AUTHORSHIP={"=Count(APP_ID)>1"}>}APP_ID)>1,APP_ID),APP_ID))

[vinieme12 · Accepted solution]

Just below will suffice

Dimension:

NATURE_OF_AUTHORSHIP

APP_ID

Measure, both will return expected output

count({<APP_ID={"=count(NAME)>1"}>}NAME)

OR

if(Count(NAME)>1,Count(NAME))

[pgloc2020]

Thank you so much for the help. 

count({<APP_ID={"=count(NAME)>1"}>}NAME) helped me to achieve desired result.

Thaks again!