Recently, a request come to me: Can DAX detect which fields has been filtered and based on that we selectively use measures?
Requirement:
Build a measure called received claims count:
If user selected any fields in the finalized specific table, [Received claims count] = [Finalized count]
If user selected any fields in the pending specific table, [Received claims count] = [Pending Count]
Other, [Received claims count] = [Finalized count] + [Pending Count]
A simplified model looks like this:
So Isfiltered or Iscrossfiltered is the solution. There are two situations:
Situation No.1, if you only have one fields in the filtered table, you should use Isfiltered
[Received claims count] =
IF (
ISFILTERED (‘FinalizedSpecificTable'[Case Number]),
[Finalized Count],
IF (
ISFILTERED ( ‘PendingSpecificTable'[Case Number] ),
[Pending Count],
[Finalized Count]+[Pending Count]
)
)
Situation No. 2, If there are more than one fields in the filtered table, and you need detect all of possible filters, you need to use Iscrossfiltered. You can either use the foreign key in the fact table or the key fields in the dimension table. Yet, please use the key field in the dimension because it performance faster.
[Received claims count] =
IF (
ISCROSSFILTERED (‘FinalizedSpecificTable'[ClaimID]),
[Finalized Count],
IF (
ISCROSSFILTERED ( ‘PendingSpecificTable'[ClaimID] ),
[Pending Count],
[Finalized Count]+[Pending Count]
)
)
Gal Love Data 
I think situation 2 should be having ‘IsCrossFiltered’ instead of ‘IsFiltered’. Right ? (I guess it was a typo/copy paste error)
LikeLike
Yes, was a typo. Thanks for catching this.
LikeLike