Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

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

 

Anonymous
Not applicable

@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

@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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.