Need Help with Filter & Calculate in Dax

[Anonymous]

Hi Everyone,

I'm trying to write a DAX expression in the pivot table and I could not figured out how to use the combination of Calculate, Filter & Count.

I need to exclude all User that has 1 or less transaction - I currently have this expression but the result is not correct: 

=if(
count([Type]) > 1, COUNT([Type]), 
blank())

The Data is as follows:

Department	User	Symbol	Type
A1	AAA	TSLA	Buy
A1	AAA	TSLA	Buy
A1	CCC	BCE	Buy
A1	DDD	PWC	Buy
B1	AAA	HBT	Sell
B1	BBB	BCE	Buy
B1	BBB	HDS	Sell

Output should be, note User "CCC" & "DDD" are not populated because it has only 1 count:

Department	User	Type	Symbol	Count
A1	AAA	Buy	TSLA	2
		Sell	HBT	1
B1	BBB	Buy	BCE	1
		Sell	HDS	1

Hope you can help!

[Anonymous]

[Exclude User] =
var __userFiltered = ISFILTERED( Data[User] )
var __oneUserVisible = HASONEVALUE( Data[User] )
var __totalTranCount =
	CALCULATE(
		COUNTROWS( Data ),
		ALLEXCEPT( Data, Data[User] )
	)
var __shouldExclude = __totalTranCount <= 1
var __finalCondition =
	__userFiltered
	&& __oneUserVisible
	&& __shouldExclude
return
	if( __finalCondition, __shouldExclude )

This measure tells you if you should exclude the user (TRUE) or not (FALSE). You can put this measure in your pivot and filter by it leaving only those users for which the measure returns FALSE (meaning: don't exclude).

Best

D

[Greg_Deckler]

Maybe:

Measure =
  VAR __Table = 
    SELECTCOLUMNS(
      FILTER(
        SUMMARIZE(
          ALL('Table'),
          [User],
          "__Count',COUNTROWS('Table')
        ),
        [__Count] < 2
      ),
      "User",
      [User]
    )
RETURN
  IF(MAX('Table'[User] IN __Table,0,1)

Then use that as a filter.