The closest thing in DAX to the MDX CurrentMember function is the HASONEVALUE function.
One of the most common uses for CurrentMember in MDX is to check if at the all level of a hierarchy. For example …
with member measures.x as
iif([Product].[Product].CurrentMember is [Product].[Product].[All Products],
'do not apply special calculation',
'apply special calculation because filtered by a product'
)
select
measures.x on 0,
[Date].[Calendar].[Calendar Year].Members on 1
from [Adventure Works]
--where [Product].[Product].&[555] //slice by a product - changes evaluation in iif
Here is the DAX equivalent. Suffice to say that when “x” is returned in the resultset, all products are in filter context (equivalent to the all member in MDX) . But when “y” is returned, only one product (Thin-Jam Hex Nut 3) is in filter context.
define
measure
'Date'[x] = if( hasonevalue('Product'[Product Name]),
"apply special calculation because filtered by a product",
"do not apply special calculation"
)
measure
'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
)
Returns
To prove that HASONEVALUE does what it says on the tin …
define
measure
'Date'[x] = countrows(values('Product'[Product Name]))
measure
'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
measure
'Date'[z] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3" ||
'Product'[Product Name] = "Bearing Ball")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
,"z", [z]
)
Returns
So “y” indeed has one value.
But what about “z” above? It has 2 values. The MDX equivalent of this is a set of products in the where clause – which CurrentMember can’t handle. And this is one of DAX’s strengths over MDX: we can deal with multi-value filters more easily.
Consider this scenario: apply a conditional calculation only if the user has filtered on one or more products (but not if there is no product filter at all).
HASONEVALUE of course won’t deal with it.
define
measure
'Date'[x] = if( hasonevalue('Product'[Product Name]),
"apply special calculation because filtered by product(s)",
"do not apply special calculation"
)
measure
'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
measure
'Date'[z] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3" ||
'Product'[Product Name] = "Bearing Ball")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
,"z", [z]
)
Returns
As “z” has 2 values in filter context, we miss it.
The ISFILTER function would catch it if we only care about filters directly on the ’Product’[Product Name] column.
define
measure
'Date'[x] = if( isfiltered('Product'[Product Name]),
"apply special calculation because filtered by product(s)",
"do not apply special calculation"
)
measure
'Date'[y] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3")
measure
'Date'[z] = calculate([x], 'Product'[Product Name] = "Thin-Jam Hex Nut 3" ||
'Product'[Product Name] = "Bearing Ball")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
,"z", [z]
)
Returns
But if the table can be filtered by other columns, we would have to check them all individually with ISFILTERED, or it won’t detect the filter.
define
measure
'Date'[x] = if( isfiltered('Product'[Product Name]),
"apply special calculation because filtered by product(s)",
"do not apply special calculation"
)
measure
'Date'[y] = calculate([x], 'Product'[Model Name] = "Chain")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
)
Returns
To detect a filter even if applied on another column on the same table, we can use ISCROSSFILTERED.
define
measure
'Date'[x] = if( iscrossfiltered('Product'[Product Name]),
"apply special calculation because filtered by product(s)",
"do not apply special calculation"
)
measure
'Date'[y] = calculate([x], 'Product'[Model Name] = "Chain")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
)
Even a parent related table’s column filter is detected – that would result in a single or a multi-value filter. In the following query, the sub-category “Bike Racks” only has one product, whereas “Touring Frames” has 18.
define
measure
'Date'[x] = if( iscrossfiltered('Product'[Product Name]),
"apply special calculation because filtered by product(s)",
"do not apply special calculation"
)
measure
'Date'[y] = calculate([x], 'Product Subcategory'[Product SubCategory Name] = "Bike Racks")
measure
'Date'[z] = calculate([x], 'Product Subcategory'[Product SubCategory Name] = "Touring Frames")
evaluate
addcolumns
(
values('Date'[Calendar Year])
,"x", [x]
,"y", [y]
,"z", [z]
)
In both cases, the filter is detected.
