Forum Discussion

AndresSalomon's avatar
AndresSalomon
Icon for Helper II rankHelper II
8 years ago
Solved

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 pr...
Need Help
  • Abduvali's avatar
    Abduvali
    8 years ago

    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

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

Abduvali's avatar
Abduvali
Icon for Skilled Sharer rankSkilled Sharer
8 years ago

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's avatar
    AndresSalomon
    Icon for Helper II rankHelper II
    8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        AndresSalomon
        Icon for Helper II rankHelper II
        8 years ago

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