cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chronis69 Member
Member

Count number of unique product sold

Hi

 

I want to calculate how many different products have been sold from the begining of the year up until the month in question and plot that in a line chart.

 

I have created the following meassure, but when I plot that in the line chart, I get a straight line at the level of the max number of different products ever sold....

 

MAX_products =
VAR unique_products = DISTINCTCOUNT(fSales[Product])
VAR return_value = CALCULATE(unique_products;
FILTER(fSales;fSales[Date]<=MAX(dDates[Date])
)
)
RETURN return_value
 
I am attaching the sample .pbix file in the link below
 
 
thnx
 
C

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Count number of unique product sold

Hi @chronis69 

 

Variables in DAX are immutable once they're assigned a value at declaration. Your 'unique_products' variable won't be affected at all within the CALCULATE. Try this instead:

 

MAX_products =
VAR return_value =
    CALCULATE (
        DISTINCTCOUNT ( fSales[Product] );
        FILTER ( fSales; fSales[Date] <= MAX ( dDates[Date] ) )
    )
RETURN
    return_value

 

4 REPLIES 4
Super User
Super User

Re: Count number of unique product sold

Hi @chronis69 

 

Variables in DAX are immutable once they're assigned a value at declaration. Your 'unique_products' variable won't be affected at all within the CALCULATE. Try this instead:

 

MAX_products =
VAR return_value =
    CALCULATE (
        DISTINCTCOUNT ( fSales[Product] );
        FILTER ( fSales; fSales[Date] <= MAX ( dDates[Date] ) )
    )
RETURN
    return_value

 

chronis69 Member
Member

Re: Count number of unique product sold

@AlB yeap, this works ... kudos....

I used the variable instead in the context of producing more clean and readable meassure.

 

is there a way of defining an expression in DAX that would work much like the way #define works in C?

ie something it won't be calcualated during definition but will be caculated when used in an expression

Super User
Super User

Re: Count number of unique product sold

@chronis69 

Where are the actual kudos? I don't see them  Smiley Happy

 

Give me an example in C of what you mean exactly.  As far as I remember, #define in C is used to create constants and macros?

Highlighted
chronis69 Member
Member

Re: Count number of unique product sold

@AlB 

 

yes basically in C if you wanted a piece of code to be replicated using a named reference you would use the define statement.

 

the compiler would practically replace at compile time the named reference with its definition and create the code...see below.

 

in the DAX equivalent, using a #define like statement, what I had originally produce would work because instead of calculating the value of the unique_product variable and then passing it to CALCULATE which would not change, it would pass to CALCULATE the not the value but the definition of the unique_product which is 

DISTINCTCOUNT(fSales[Product])

 

do you understand what I mean?

 

#include <stdio.h>

#define NAME "TechOnTheNet.com"
#define AGE 10

int main()
{
   printf("%s is over %d years old.\n", NAME, AGE);
   return 0;
}