DAX – Isfiltered and Iscrossfiltered

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:

BI diagram

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]

)

)

2 thoughts on “DAX – Isfiltered and Iscrossfiltered

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: