Forum Discussion

gregv5577's avatar
gregv5577
Regular Visitor
8 years ago
Solved

using ALL or ALLEXCEPT to create measure which ignores multiple slicers

I have a report which generates several versions of the same measure using or ignoring specific slicer selections. I can only get this to work if I construct my slicers from the fact table itself. I could just carry my labels on my fact table but that seems pretty messy. I'm either using the wrong DAX or I have to change the way my fact and dimension tables realate to one another. Below is a look at the statement which does work but only when I focus everything on the fact table. Also there is a simplified picture of the data. Any suggestions would be appreciated. Thanks. 

 

Measure 3 = CALCULATE(AVERAGE(OrderFactTable[Revenue]), ALL(OrderFactTable[employee_id], OrderFactTable[product_id]))

 

This works but I'd prefer to do something like this below. Can't seem to get the DAX. 

 

Measure 3 = CALCULATE(AVERAGE(OrderFactTable[Revenue]),ALL(Employee[employee_name], Product[product_name]))

 

 

 

 

Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    Hi gregv5577,

     

    You can create measures below: 

    Measure 1 = AVERAGE('OrderFactTable'[revenue])

    Measure 2 =
    DIVIDE(CALCULATE (
    SUM ( OrderFactTable[revenue] ),
    ALL ( Employee[employee_name] )
    ), CALCULATE (
    DISTINCTCOUNT(OrderFactTable[product_id]),
    ALL ( Employee[employee_name] )
    ) )

     

    Measure 3 =
    DIVIDE(CALCULATE (
    SUM ( OrderFactTable[revenue] ),
    ALL ( Employee[employee_name] ),ALL('Product'[product_name])),
    CALCULATE (
    DISTINCTCOUNT(OrderFactTable[company_id]),
    ALL ( Employee[employee_name] ),ALL('Product'[product_name])))

     

     

    You can see attached pbix file. 

     

    Best Regards,
    Qiuyun Yu

  • BILASolution's avatar
    BILASolution
    8 years ago

    Hi gregv5577

     

    As an alternative...

     

     

     

    Measures:

     

    Total Revenue = SUM(OrderFactTable[revenue]) 
    Measure 1 = [Total Revenue] 
    Measure 2 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]) )
    Measure 3 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]);ALL('Product'[Product_name]))

    I hope this helps

     

    Regards

    BILASolution

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi gregv5577,

     

    You can create measures below: 

    Measure 1 = AVERAGE('OrderFactTable'[revenue])

    Measure 2 =
    DIVIDE(CALCULATE (
    SUM ( OrderFactTable[revenue] ),
    ALL ( Employee[employee_name] )
    ), CALCULATE (
    DISTINCTCOUNT(OrderFactTable[product_id]),
    ALL ( Employee[employee_name] )
    ) )

     

    Measure 3 =
    DIVIDE(CALCULATE (
    SUM ( OrderFactTable[revenue] ),
    ALL ( Employee[employee_name] ),ALL('Product'[product_name])),
    CALCULATE (
    DISTINCTCOUNT(OrderFactTable[company_id]),
    ALL ( Employee[employee_name] ),ALL('Product'[product_name])))

     

     

    You can see attached pbix file. 

     

    Best Regards,
    Qiuyun Yu

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    Hi gregv5577

     

    As an alternative...

     

     

     

    Measures:

     

    Total Revenue = SUM(OrderFactTable[revenue]) 
    Measure 1 = [Total Revenue] 
    Measure 2 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]) )
    Measure 3 = CALCULATE([Total Revenue];ALL(Employee[Employee_name]);ALL('Product'[Product_name]))

    I hope this helps

     

    Regards

    BILASolution

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        Hi gregv5577,

         

        If our suggestions meet your requirement, would you please mark helpful replies as answers so we can close this thread? :smileyhappy:

         

        Best Regards,
        Qiuyun Yu