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

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

5 Replies

  • 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