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

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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors