cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 III
Super User III

Re: Count number of unique product sold

Hi @Anonymous 

 

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

 

View solution in original post

4 REPLIES 4
Super User III
Super User III

Re: Count number of unique product sold

Hi @Anonymous 

 

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

 

View solution in original post

Anonymous
Not applicable

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 III
Super User III

Re: Count number of unique product sold

@Anonymous 

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?

Anonymous
Not applicable

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;
}

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors