cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndresSalomon Regular Visitor
Regular Visitor

Perform a measure when a filter is active

Hi all, it has been a while without doubts (it seems I'm learning and is because of the people here, thanks!!), but now I come with a doubt that I'm not being able to find a solution. Is something pretty simple (the idea only).

 

I have two measures, [Measure1] and [Measure2]. They are very similar, only compute the aggregation of 2 different columns. 

 

In my table I have a column (call it myTable[Type]) that I use as a slicer with two values: Payroll or Expenses. 

 

So what I need is a global measure [GlobalMeasure] that "knows" if the user selects Payroll in the slicer to use [Measure1] and if the user selects Expenses in the slicer to use [Measure2].

 

Please let me know if you need more details or don't understand anything. Thanks in advance, as always!

 

Kind regards,

 

Andy.-

2 ACCEPTED SOLUTIONS

Accepted Solutions
Abduvali Established Member
Established Member

Re: Perform a measure when a filter is active

Hi @AndresSalomon,

 

Try creating a column with IF statement:

 

Condition = IF(Table[Type] = "Payroll", 1,0)

 

and 

 

Global Measure = 

Var M1 = measure1

Var M2 = measure2

 

 

 

 

Return

 

if(table[condition]>0,M1,if(table[condition]=0,M2,0) //if you calculating on different columns then it will be blank anyway

 

 

 

 

 

This might do a trick for you.

 

Regards

Abduvali

Moderator v-yuezhe-msft
Moderator

Re: Perform a measure when a filter is active

@AndresSalomon,

Create the condition column as Abduvali's post in the dimension table, and create the global measure using dax below in the dimension table. You will get expected result when using dimtype slicer  to filter your visual.

Global Measure =  
Var M1 = [Measure 1]
Var M2 = [Measure 2]
Return
if(MAX('dimension'[Condition])>0,M1,if(MAX('dimension'[Condition])=0,M2,0))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Abduvali Established Member
Established Member

Re: Perform a measure when a filter is active

Hi @AndresSalomon,

 

Try creating a column with IF statement:

 

Condition = IF(Table[Type] = "Payroll", 1,0)

 

and 

 

Global Measure = 

Var M1 = measure1

Var M2 = measure2

 

 

 

 

Return

 

if(table[condition]>0,M1,if(table[condition]=0,M2,0) //if you calculating on different columns then it will be blank anyway

 

 

 

 

 

This might do a trick for you.

 

Regards

Abduvali

AndresSalomon Regular Visitor
Regular Visitor

Re: Perform a measure when a filter is active

Hi @Abduvali, thank you very much for replying! 

 

That makes sense. What would happen if I complex the data model a bit?

I have now two data tables Table1 and Table2 and the relation between them is with the dimension table dimType, that has the column with the two values: Payroll and Expenses. And [Measure1] aggregate a column from Table1 and [Measure2] aggregates a column from Table2.

 

If I use that dimension table to filter, how can adapt the solution you gave? Thanks in advance again!! 

 

Kind regards,

 

Andy.-

Moderator v-yuezhe-msft
Moderator

Re: Perform a measure when a filter is active

@AndresSalomon,

Create the condition column as Abduvali's post in the dimension table, and create the global measure using dax below in the dimension table. You will get expected result when using dimtype slicer  to filter your visual.

Global Measure =  
Var M1 = [Measure 1]
Var M2 = [Measure 2]
Return
if(MAX('dimension'[Condition])>0,M1,if(MAX('dimension'[Condition])=0,M2,0))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AndresSalomon Regular Visitor
Regular Visitor

Re: Perform a measure when a filter is active

Hi @v-yuezhe-msft & @Abduvali, your solutions works great!! Thank you very much!!

 

Now just playing with DAX I found another working solution, based on this post from Marco & Alberto:

 

http://www.daxpatterns.com/parameter-table/

Check it:

 

First no need to create an extra column, you will only use the 2 values from the dimension table (Payroll and Expenses). Also just thinking if worth to create the two VARs.. why don't use the two measures directly? VAR improves the performance? If not I think there is extra code here that is not needed. 

GlobalMeasure=

VAR M1 = [Measure1]
VAR M2 = [Measure2]

RETURN

IF(
HASONEVALUE ( dimType[Type] ),
SWITCH (
VALUES ( dimType[Type] ),
"Payroll", M1,
"Expenses", M2
),
BLANK()
)

Let me know your thoughts! Just a variant of your solutions. Thanks again for the help, you are awesome.

 

Kind regards,

 

Andy.-

Highlighted
Abduvali Established Member
Established Member

Re: Perform a measure when a filter is active

You are very welcome @AndresSalomon!

 

To be honest, use whatever works for you best and easier for you to use!!!

 

But there is always a best practice around =D so by using Variables its easier to read your DAX by other people, its cleaner code and it can be very complex and still work well.

 

But if you like to know more here is some reading for you enjoy:

  1. Using Variables in DAX
  2. Variables in DAX

 

 

Regards

Abduvali