Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am not sure if this is possible in DAX but this is my question:
Therefore I created a simple test table with the name POWERBI_TEST_3:
ORDERNUMBER Unique. The Order starts on one date en had Ended on a date.
I want to know on each day in January 2019 (or more months) how many Orders have de status ‘Open’. The result should look like this, I therefore created a the table with the name DateJanuary with every date in January, a rownumber and a empty column for the numbers of open orders.
I created this SQL query to do the job.
In SQL I created a The Query to do the job look like:
DECLARE @COUNTER1 INT
DECLARE @NR_OPEN_ORDERS INT
DECLARE @DATJAN DATE
SET @COUNTER1 = 1
WHILE @COUNTER1 <= (SELECT COUNT(*) FROM Date_January)
BEGIN
SET @DATJAN = (SELECT Date_In_January FROM Date_January WHERE ROW_NMBR = @COUNTER1)
SET @NR_OPEN_ORDERS = (SELECT COUNT(DISTINCT ORDERNUMBER) AS AANTAL FROM POWERBI_TEST_3
WHERE (DATEDIFF(DAY, START_ORDER_DATE, @DATJAN) > 0
AND DATEDIFF(DAY, @DATJAN, END_ORDER_DATE) > 0)
OR
(DATEDIFF(DAY, START_ORDER_DATE, END_ORDER_DATE) = 0
AND START_ORDER_DATE = @DATJAN))
UPDATE Date_January
SET NR_OPEN_ORDERS = @NR_OPEN_ORDERS
WHERE Date_In_January = @DATJAN
SET @COUNTER1 = @COUNTER1 + 1
END
The end result looks like:
In the SQL query I look at every record step by step per rownnumber. I don’t know if this is possible in DAX (or Power Query).
For some reason it’s not possible for me to use this SQL query. I am looking for a solution in PowerBI itself.
First off, this is the set-based T-SQL that does all of this at least an order of magnitude faster than the procedural code:
UPDATE jan
SET
jan.NR_OPEN_ORDERS = c.OrderCount
FROM dbo.Date_January AS jan
CROSS APPLY (
SELECT
count(*) AS OrderCount
FROM dbo.PowerBI_test_3 AS pbi
WHERE (
pbi.Start_Order_Date <= jan.[Date_in_Jan]
and
jan.[Date_in_Jan] <= pbi.End_Order_Date
)
) AS c;
Secondly, if you have a PBI table with dates, say, Calendar, and want to know the number of open order on each day, you create a calculated column in Calendar with this formula:
[Open Orders] =
var __date = 'Calendar'[Date]
var __orderCount =
COUNTROWS(
filter(
'Orders',
'Orders'[start_order_date] <= __date
&&
__date <= 'Orders'[end_order_date]
)
)
return
0 + __orderCount
Best
D
Many thanks for the query and DAX script. I was not familiar with APPLY operators. Great to know now. DAX query also runs fine.
Regards Rob
Hi @RvdC ,
Try this measure:
_openOrders =
VAR date_to_examine =
MAX('calendar'[Date])
VAR number_orders =
CALCULATE(
DISTINCTCOUNT(orders[orderNumber]),
KEEPFILTERS(date_to_examine > orders[startDate]),
KEEPFILTERS(date_to_examine < orders[enddate])
)
RETURN
IF (ISBLANK(number_orders), 0, number_orders)
I get the following output:
*NB* The calendar table DOES NOT/MUST NOT have a relationship with the orders table.
Good luck
Proud to be a Datanaut!
Hi!
I have just confirmed that the solution to all my problems is to not link the calendar table to the table I am getting the info about the open items from like you said before, but I don't understand why. Can you explain why is that the solution, please?
Thanks in advance
Hi @Roberto_Campelo ,
This is because a relationship between the calendar and orders tables will force the orders table to be filtered by each calendar date due to row context.
For example, if there is a relationship between calendar[date] and orders[start date], and if the calendar date on the row is 01/01/2022, then this filters the order table to only rows where [start date] = 01/01/2022. This prevents us from being able to evaluate our measure across the full range of [start date] and [end date] values, as we need to.
There are a couple of ways around this other than using a disconnected date table:
- You can use the CROSSFILTER() function to 'switch off' the relationship (much like USERELATIONSHIP() 'switches on' relationships).
OR
- You can use functions like ALL(), ALLEXCEPT() etc. to reopen up all the values that have been filtered out by the relationship.
Pete
Proud to be a Datanaut!
Hi Pete,
Thanks a lot for your explanation, it was really useful. Since I need to keep the relationship through the date, I used the CROSSFILTER() function and it worked perfectly. And now I understand how this row context works.
Roberto
Thanks for the quick answer! I have tried the DAX script in Power BI on my Dummy dataset. It got only zeros as output in the added _openOrder2 column.
Maybe I overlooked something in the script?
The DAX I provided was for a measure as stated, not a calculated column. Apologies if I've misunderstood your requirement here.
It does, however, provide the output you were looking for when applied to visualisations.
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |