Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
@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
@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
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 :
Hi,
Do you get the correct result if you remove the "" from your formula?
@Ashish_Mathur just have another query
Hi,
That formula seems fine to me. Share the download link of your PBI file. Show me exactly what/where the problem is
Thanks! This opens up some new capabilities. Works great. Thanks for posting.
Thank you, it worked!!
@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.
@Rubenvw Okay great!
Here's a little more info on FILTER arguments in CALCULATE
http://www.sqlbi.com/articles/filter-arguments-in-calculate/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |