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
lprbi
Advocate I
Advocate I

DAX: BUG? COUNT HOW MANY PRODUCTS I HAVE THRU ORDERS[DATE], EVEN WITHOUT SALES

Hi,

Using PowerBI I having a big trouble with a task that seems simple: I need giving accross time (ORDER[DATE]), count how many products I have inserted in my database, even with no sales. I have a column for that: PRODUCTS[DT_INSERT]. Tried some, this give a correct value in the first YEAR, and wrong in th others:

CALCULATE(
COUNT(PRODUCTS[PROD_ID]);
CROSSFILTER(PRODUCTS[PROD_ID];ORDER_ITENS[PROD_ID];None);
FILTER(VALUES(PRODUCTS[DT_INSERT]);DATEVALUE(PRODUCTS[DT_INSERT])<=DATEVALUE(ENDOFMONTH(LASTDATE(ORDERS[ORDER_DATE]))))
)

Using DATEVALUE or not have no effect.

The total should give 2169 in 2015.


Please helpUntitled.png

1 ACCEPTED SOLUTION

Count of products all-time: (probably equal to the number of rows in the Products table)
 
COUNTROWS(ALL(Products[ProductId]))
 
Running count of products, assuming there's a column that says the date it was added
 
    CALCULATE(
        COUNTROWS(VALUES(Products[ProductId])),
        ALL(Products),
        FILTER(ALL(Products), Products[AddedDate]<=MAX(Dates[Date])))

View solution in original post

2 REPLIES 2
lprbi
Advocate I
Advocate I

I succesfully counted. Importing the table PRODUCTS twice (without any relationships)...

Tried crossfilter all relations on PRODUCTS no luck.

Count of products all-time: (probably equal to the number of rows in the Products table)
 
COUNTROWS(ALL(Products[ProductId]))
 
Running count of products, assuming there's a column that says the date it was added
 
    CALCULATE(
        COUNTROWS(VALUES(Products[ProductId])),
        ALL(Products),
        FILTER(ALL(Products), Products[AddedDate]<=MAX(Dates[Date])))

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.