cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rubenvw
Advocate I
Advocate I

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
Sean
Community Champion
Community Champion

@Rubenvw Here's the Measure I would use Smiley Happy

 

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 Smiley Happy

View solution in original post

11 REPLIES 11
Laurier31
Helper I
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
 

 

Sean
Community Champion
Community Champion

@Rubenvw Here's the Measure I would use Smiley Happy

 

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 Smiley Happy

View solution in original post

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

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Jay_O
Frequent Visitor

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

Thank you, it worked!!

ankitpatira
Community Champion
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.

 

Capture.JPG

 

 

 

 

 

 

 

 

 

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.

Sean
Community Champion
Community Champion

@Rubenvw Okay great! Smiley Happy

 

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

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

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors