[HELP] How to create my first DAX

[tiago]

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.

1. A table with all the products (unique values) and attributes – This table is called PRODUCTS.
2. 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
3. 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

[Greg_Deckler · Accepted solution]

In general:

Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0))

[Greg_Deckler · Accepted solution]

In general:

Measure = CALCULATE(COUNT(Table[Column]),FILTER(Table,[DISCOUNT] > 0))

[tiago]

Hi 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))

[tiago]

smoupre, 

Thank you for the quick response, I will try what you suggested, but DISCOUNT is on the TABLE PRODUCTS_PRICES and Item_Sold is on the TABLE SOLD_ITEMS the way you suggesting DISCOUNT and Item_Sold had to be on the same table no ?

Edit: 

As I imagined, it doesnt work, it says that the colum Item_Sold was not found. If you could suggest how to do it would be great.

Thx

[Greg_Deckler · Accepted solution]

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))

[tiago]

Smoupre,

Thank you for the reply, definitely will study DAX, but your help was a great start to understand the language. Apparently you last suggestion worked.

Regards,

Tiago