Forum Discussion
Helper IIPerform 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,
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
Skilled SharerHi 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
Helper IIHi 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.-
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 Anonymous & 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:
Regards
Abduvali