Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

DAX: sum with two filters

Hi all,   I am trying to make a new measure 'SalesWest' in Power BI Desktop but I can not make it work.   The situation: there are two tables, Table 1 (factSales) and a related Table 2 (dimStores...
Need Help
  • Sean's avatar
    Sean
    10 years ago

    Anonymous Here's the Measure I would use :smileyhappy:

     

    West Sales (Qty NOT 2) MEASURE = 
CALCULATE (
    SUM ( factSales[amount] ),
    FILTER ( factSales, factSales[storeid] = 1 && factSales[quantity] <> 2 )
)

     

    However the way you ask the question it seems you want to filter each table separately???

     

    West Sales (Qty NOT 2) MEASURE 2 = 
CALCULATE (
    SUM ( factSales[amount] ),
    FILTER ( dimStores, dimStores[name] = "West" ),
    FILTER ( factSales, factSales[quantity] <> 2 )
)

     

    Both of these Measures should work :smileyhappy:

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

Anonymous Here's the Measure I would use :smileyhappy:

 

West Sales (Qty NOT 2) MEASURE = 
CALCULATE (
    SUM ( factSales[amount] ),
    FILTER ( factSales, factSales[storeid] = 1 && factSales[quantity] <> 2 )
)

 

However the way you ask the question it seems you want to filter each table separately???

 

West Sales (Qty NOT 2) MEASURE 2 = 
CALCULATE (
    SUM ( factSales[amount] ),
    FILTER ( dimStores, dimStores[name] = "West" ),
    FILTER ( factSales, factSales[quantity] <> 2 )
)

 

Both of these Measures should work :smileyhappy:

  • imy's avatar
    imy
    Icon for Helper I rankHelper I
    7 years ago

    Thank you, it worked!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks! This opens up some new capabilities. Works great. Thanks for posting.

  • chavanr's avatar
    chavanr
    Icon for Resolver I rankResolver I
    5 years ago

    Hi Sean 

    Just a quick question on this one, I am doing the same thing to calculate a measure but it is giving me error "dax comparison operations do not support comparing values of type. consider using the value or format functione Integer with values of type text"

     

    The data type for all the columns is "whole number"

     

    Formula :

    Revenue Act = CALCULATE(
    SUM(FactWeighing[Value]),
    FILTER(DimMainAccount,DimMainAccount[Post Collections Revenue Excl flag] ="0"),
    FILTER(DimFinancialDimension,DimFinancialDimension[A_BusinessGroup Code] ="2"
    )
    )
     
    Can you please have a look into this one?
     
    Many thanks

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      Do you get the correct result if you remove the "" from your formula?

      • chavanr's avatar
        chavanr
        Icon for Resolver I rankResolver I
        5 years ago

        Ashish_Mathur  just have another query

        Weighing Status Revenue include = IF(FactWeighing[Weighing Status] ="Closed" || FactWeighing[Weighing Status] = "Closed MOL",1,0)
         
        In the above formula, the dax measure is not identifying "Closed MOL" & hence not putting it as flag-1, probably because it has space.
         
        Any solution?
         
        Many thanks!!