Forum Discussion
Helper I[HELP] How to create my first DAX
Hi there,
I work for a retail company and I a newbie on POWER BI. I have good knowledge in Excel and I am starting to learn POWERBI DAX. I am trying to do simple things to see If I can start to understand how POWER BI works. I never did a calculated field, at least not one that worked as I wanted it to. So, If someone could point me on the right direction I would be glad.
I want to do a simple report with the number of items sold on the past 10 days and how many of that items where in SALE. I was able to setup the MATRIX to show the date with the past 10 days and the number of items sold per establishment, but I don’t know how to do a count when the PRODUCT is on SALE.
My set of data has all the proper relationships made and it is distributed like this.
- A table with all the products (unique values) and attributes – This table is called PRODUCTS.
- A table with all the products sold per establishment and date. (I am using the FIELD DATE as ROW on MATRIX, the FIELD Items_Sold as VALUE and the FIELD BRANCH as COLUMN in the MATRIX) This table is called SOLD_ITEMS
- A table with UNIQUE values with all the PRODUCTS and PRICES and the % of discount of the ITEM. This table is CALLED PRODUCTS_PRICES and has the following FIELDS PRODUCTS, PRICE, DISCOUNT. This TABLE has RELATION with PRODUCTS TABLE and the PRODUCTS table have RELATION with the SOLD_ITEMS table.
My question is how do I create a calculated field that checks if the field DISCOUNT > 0 and count it. I know that I need to add PRODUCT to the matrix so it can bring the discount for each product sold and count them, but I don’t know how to do it.
Thanks in advance!
Regards,
Tiago
In general:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0))
Try putting the table name in front of the second column. You'll definitely need to have all of the tables related to one another. Posting some sample/mock data would be very beneficial to posting a solution that would definitely work. You can also look into RELATED and RELATEDTABLE functions. For example, you could do things like:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table[DISCOUNT] > 0), FILTER(RELATEDTABLE(Table2), [Item_Sold] > 0))
6 Replies
- Greg_Deckler
Community Champion
In general:
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0))
tiagoHi SMOUPRE,
You are the best. It worked!!! Only thing that is missing is that I need to count just if Item_Sold > 0 how to add that to your expression?
What happened was that sometimes, there is an item that is returned and this item gets stored on database like this Item_Sold = -1. With the measure that you helped me to create if this product that was returned was on SALE it gets counted like a sold item.
Basically I need to add to that measure an IF to the count so it would coult only if Item_Sold >0.
Thanks you for the help!
- Greg_Deckler
Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0 && [Item_Sold] > 0))