cancel
Showing results for
Did you mean:  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). Now I would like to make a measure that sums all sales where the Store = West (Lookup to dimStores) and Sales Quantity is not equal to 2 (SalesQuantity within factSales).

Table 1 / factSales
-----------------------------
id | storeid | quantity | amount
-----------------------------
1 |   1  |    1      |  100
2 |   1  |    1      |  100
3 |   3  |    2      |  200
4 |   4  |    3      |  300
5 |   4  |    3      |  300
6 |   4  |    6      |  600

Table 2 / dimStores
-----------------------------
storeid | name
-----------------------------
1  |    west
2  |    north
3  |    east
4  |    south

I am trying to use the calculated function, but when I try to insert two filters, I got an error that says: "The value cannot for "..." cannot be determined. Either "..." doesnt exist, or there is not a current row for a column named "...".

Any suggestions which function I should use?

1 ACCEPTED SOLUTION  Community Champion

@Rubenvw Here's the Measure I would use 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 11 REPLIES 11  Helper I
hi,
J tried to use the function DAX ( in Desktop) in using 2 differents tables. it doesn't work
Table A as incident
Number             Age_Diff
inc000000            2
inc000001          50

Table B  as Table_Parm_Age
Code 1   Code 2   Min   Max        Short Desc
Age         1            0        3           1 - from 0 to 3 days
Age          2            2        7          2 - from 2 to 7 days
Age          3            6      60          3 - from 6 to  60  days
Age          4            59    999999   4 - more than 59 days

My goal is to merge these 2 tables to One table with filters without a relationship because there are  2 conditions : min and max
=> as in SQL
select
* from
Table A, Table B
where B.Min >= A.Age_diff and Age_Diff < B.Max ( normally, with B.code1 ="Age" because, I enough did the filter in Query)

Result  :
Table_join_inc_parm = FILTER(CROSSJOIN(incident;Table_Parm_Age); (incident[Age_Diff - Entier] >= Table_Parm_Age[Min] && incident[Age_Diff - Entier] < Table_Parm_Age[Max] ) ).

it doesn'work because, count of table A ( incident) < count final ( Table_join_inc_parm)  .
==> I would like that 2 counts are equal.

I tried an another method
Table 2 = GENERATEALL ('incident';VAR Table1Age = 'incident'[Age_Diff]RETURNSELECTCOLUMNS (CALCULATETABLE ( 'Table_Parm_Age'; 'Table_Parm_Age'[Code1] ="Age" && 'Table_Parm_Age'[Min] >= 'incident'[Age_Diff - Entier] && Table1Age < 'Table_Parm_Age'[Max] );"Desc"; 'Table_Parm_Age'[Short Desc]))

Here, all columns of incidents and ONE column of Table_Parm_Age as Short Desc   are installed in a result table as TABLE2.

It doesn't work too.

Then How to do it to correct a good result for the SAME count between table incident and  result Table_join_inc_parm as Table 2 or Table
Thank you in advance to help me .
sincerely  Community Champion

@Rubenvw Here's the Measure I would use 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   Resolver I

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"),
)
)

Can you please have a look into this one?

Many thanks  Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com  Resolver I

@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!!  Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com Frequent Visitor

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

Thank you, it worked!!  Community Champion

@RubenvwTo be able to "make a measure that sums all sales where the Store = West (Lookup to dimStores) and Sales Quantity is not equal to 2" do this, you don't need to make a measure or calculated column using DAX. You can do this by usin basic power bi features.

1. In power bi desktop under relationship view ensure store id from fact table is joined with store id from dimension table. 2. Then simply use your visual for example card visual and drop Amount field from first table onto it. This will give you total sales. Then from second table use Name (store name) field to filter it to West or use it as a slicer. Similarly use quantity from first table and limit it to not equal to 2 by using filter or slicer.  @ankitpatiraThanks for your reply! Yes, that's also an option. In this case I would like to make it easy as possible for end-users, by providing them some measures at the beginning..

@Sean The second option works fine for me! Thank you so much:) It really helps me.  Community Champion

@Rubenvw Okay great! http://www.sqlbi.com/articles/filter-arguments-in-calculate/   