cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Return filtered column if any in a measure as a variable

Hello everyone

I am trying to create a one-size-fits-all measure for multiple tables, but I got stuck somewhere.

Below is a dummy case:

Raw Data

 Year Store Product Location Revenue 1/1/2019 A Ice Cream New York \$           500 1/1/2019 A Beer New York \$           500 1/1/2019 B Ice Cream Little Rock \$           100 1/1/2019 B Beer Little Rock \$           500 1/1/2019 B Chips Little Rock \$           450 1/1/2019 C Ice Cream Denver \$           300 1/1/2019 C Beer Denver \$           600 1/1/2019 C Pizza Denver \$        1,000 1/1/2019 C Chips Denver \$           200 1/1/2020 A Ice Cream New York \$           350 1/1/2020 A Beer New York \$           450 1/1/2020 A Chips New York \$        1,000 1/1/2020 B Ice Cream Little Rock \$           200 1/1/2020 B Beer Little Rock \$           500 1/1/2020 B Pizza Little Rock \$           450 1/1/2020 B Chips Little Rock \$           350 1/1/2020 C Beer Denver \$           200 1/1/2020 C Chips Denver \$           100

To get the difference/ change between the revenue mix of 2019 and 2020, I created a measure "Diff_Mix_btw_Years_Store"

Diff_Mix_btw_Years_Store =

var _current_mix_ = DIVIDE(SUM(Store[Revenue]),CALCULATE(SUM(Store[Revenue]),ALL(Store[Store])))

return

CONCATENATE(FIXED((_current_mix_ - _previous_mix_)*100,1)," pts")

I returned what I need (B-A)

However, if I want to get the B-A value after replacing the row filter with either "product" or "location", I will have to create new measures and replace the Store[Store] in the above measure with Store[Product] or Store[Location]

Like this:

Diff_Mix_btw_Years_Product =

var _current_mix_ = DIVIDE(SUM(Store[Revenue]),CALCULATE(SUM(Store[Revenue]),ALL(Store[Product])))

return

CONCATENATE(FIXED((_current_mix_ - _previous_mix_)*100,1)," pts")

Is there any way to create one and the only measure that can return the result (B-A) for any row filter that users may apply? In this case, they are Store[Store] , Store[Product] or Store[Location].

I do not know if there is any DAX that can detect which column is used as a row filter and send to ALL( ) as a variable.

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

According to your description, I think you can simply modify your measure.

Like this:

``````measure =
VAR _current_mix_ =
DIVIDE (
SUM ( Store[Revenue] ),
CALCULATE (
SUM ( Store[Revenue] ),
ALL ( Store[Store] ),
ALL ( Store[Product] )
)
)
VAR _previous_mix_ =
DIVIDE (
CALCULATE ( SUM ( Store[Revenue] ), DATEADD ( Store[Year].[Date], -1, YEAR ) ),
CALCULATE (
SUM ( Store[Revenue] ),
DATEADD ( Store[Year].[Date], -1, YEAR ),
ALL ( Store[Store] ),
ALL ( Store[Product] )
)
)
RETURN
CONCATENATE ( FIXED ( ( _current_mix_ - _previous_mix_ ) * 100, 1 ), " pts" )``````

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @Anonymous

According to your description, I think you can simply modify your measure.

Like this:

``````measure =
VAR _current_mix_ =
DIVIDE (
SUM ( Store[Revenue] ),
CALCULATE (
SUM ( Store[Revenue] ),
ALL ( Store[Store] ),
ALL ( Store[Product] )
)
)
VAR _previous_mix_ =
DIVIDE (
CALCULATE ( SUM ( Store[Revenue] ), DATEADD ( Store[Year].[Date], -1, YEAR ) ),
CALCULATE (
SUM ( Store[Revenue] ),
DATEADD ( Store[Year].[Date], -1, YEAR ),
ALL ( Store[Store] ),
ALL ( Store[Product] )
)
)
RETURN
CONCATENATE ( FIXED ( ( _current_mix_ - _previous_mix_ ) * 100, 1 ), " pts" )``````

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.