Forum Discussion

MP_123's avatar
MP_123
Microsoft Employee
9 years ago
Solved

Filter with calculated measures

hi, i know that calculated measures is changing regard it's context. i'm using a 'Text' type calculated measure to filter another calculated measure. since it's value is not stable, filter with ca...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    MP_123

    how can i filter with dynamically filter?

    Have you tried the VAR function that I mentioned in your previous thread? In this scenario, the formula should like below.

    = 
    VAR m = Measures
    RETURN
    CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column = m ) )

    If the result is still not right, could you post your table structure and the measures you use, and some sample data in your case?:smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

MP_123

how can i filter with dynamically filter?

Have you tried the VAR function that I mentioned in your previous thread? In this scenario, the formula should like below.

= 
VAR m = Measures
RETURN
CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column = m ) )

If the result is still not right, could you post your table structure and the measures you use, and some sample data in your case?:smileyhappy:

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Can somone tell me why this works

    = 
    VAR m = Measures
    RETURN
    CALCULATE ( SUM ( 'x' ), FILTER ( 'y', column = m ) )

    and this does not:

    CALCULATE ( SUM ( table1[x] ), FILTER ( table1, table1[y] = measure ) )

    I had a sample excel where it worked fine in but i had to implement your VAR method for my SSAS and powerpivot model....

  • MP_123's avatar
    MP_123
    Microsoft Employee
    9 years ago

    v-ljerr-msftthanks! your awsome!

    • amalhotr's avatar
      amalhotr
      New Member
      8 years ago

      VAR m = [Measure]
      RETURN
      CALCULATETABLE('Table 2','Table 2'[QUARTER]=m) Not working with calculatedtable DAX function

      • Michiel's avatar
        Michiel
        Resolver III
        8 years ago

        What is the error that you get? You cannot use this directly in a measure, but that is because a measure must return a scalar value, not a table (which is wat CALCULATEDTABLE returns). So at least it should be wrapped in a table aggregation function.

  • DesireeMaya22's avatar
    DesireeMaya22
    New Member
    8 years ago

    Hi

    I tried the same thing with a filtren that you can choise in the report (a visual filter)

     

    Viajes de Cir - Dest 2 = IF([Base Ajustada Origen]= "HID",CALCULATE(COUNTROWS(Circuitos),'Circuitos'[MAPA DESTINO]="HID"),4)

     

    Measure = Base Ajustada Origen, the value that it generate is HID

     

    But it not recognize the mearsure like a text. The messeage said that i canĀ“t used a mesure with a dinamic visual.

     

    could you help me?

  • MS_1234's avatar
    MS_1234
    New Member
    4 years ago

    Hi, 

     

    I have a table with 3 Columns
    Report Name | Manager | Status

    Status contains Values: Pass, Fail, NA

     

    I want to calculate % Pass for each manager. NA should be calculated in %of calculation.
    I created one measure to calculate Total of Pass and Fail. and tried creating another measure for only "Pass". But its same % value for each manager. 

     

    I am showing this data in Matrix. Please guide. Thanks!

  • khalidwaleed's avatar
    khalidwaleed
    Frequent Visitor
    4 years ago

    Hi v-ljerr-msft , I was also stuck with this problem for more than 2 days till I came across this answer. Can you please briefly tell why it works. I am totally clueless.