Perform a measure when a filter is active

[AndresSalomon]

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.-

[Abduvali · Accepted solution]

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

[Abduvali · Accepted solution]

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]

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.-

[v-yuezhe-msft · Accepted solution]

@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

[AndresSalomon]

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.-

[Abduvali]

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