DAX: sum with two filters

[Anonymous]

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?

[Anonymous]

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

[Sean · Accepted solution]

@Anonymous 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

[chavanr]

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]

Hi,

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

[chavanr]

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

[Ashish_Mathur]

Hi,

That formula seems fine to me.  Share the download link of your PBI file.  Show me exactly what/where the problem is

[Anonymous]

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

[imy]

Thank you, it worked!!

[ankitpatira]

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

[Anonymous]

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

[Sean]

@Anonymous Okay great!

Here's a little more info on FILTER arguments in CALCULATE

http://www.sqlbi.com/articles/filter-arguments-in-calculate/