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.
Hi everyone,
I am trying to see if I can transpose this SQL query into PowerBI.
Basically, I have a count that I want to make month by month but the trick is to excluse certain values based on a start date and a end date for each month.
For the start date, I want to count everything that comes before the end of the month (including previous months).
For the end date, I want to count everything that comes after the end of the month (including next months).
Example in SQL :
SEL customer_id, '
202101' _PERIOD,
START_DT,
END_DT,
count(distinct products)
FROM Table 1
Where (assign_start_dt and <=1210131 end_dt >= 1210131)
I started something in dax for the start date but when it comes to the end date, I don't know how to proceed.
I have a date calendar and I tried something with userelationship to no avail.
Solved! Go to Solution.
I found a solution based on SQLBI's info
Your SQL query is missing the "group by" part and there are typos in the "where" clause.
Please show your data model. Do you have an active and inactive relationship between the calendar table and the Query1 table?
You can't use MAX inside CALCULATE's filter list. Use variables to prevent filter context impact.
I may have been unclear. I am not trying to pass the query as it is in Powerbi
I am trying to filter on the dates in DAX and not in SQL.
So here is the query that I have actually used. The where clause works fine.
SEL distinct
cust_id,
START_DT,
END_DT,
COUNT(distinct na) as NA
FROM Table 1
WHERE (contr_type IN ('INT','FIT' ) OR ADSL_PRODUCT_IND = 1)
group by 1,2,3
Here is my data model. I do have an active and an inactive relationship between the calendar table and the query1 table
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 |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |