Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi folks,
I have a query which needs to be implemented in Power BI.
I have two tables. MARTSITEMS and DIM_Date
I want to inner join both the tables and get a aggregated table for Monthly. After getting this aggreagted table, I also want to use slicers from MARTITEMS so that slice and dice will also happen on aggreagted table. I dont know whether to use M code or DAX to perform this query. Please help.
Below is the query:
select dt.date, count(distinct kunnr), sum(dmbtr) as total_receivables,
sum(case when dt.date > di.duedate then di.dmbtr else 0 end) as Total_overdue,
Total_overdue/total_receivables as overdue_percent,
1-overdue_percent as not_due_percent,
from DEV_MARTSITEMS di
inner join (select last_day_month as date from DEV_DIM_DATE
group by last_day_month) dt
on (dt.date between di.budat and di.augdt ) or (di.augdt is null and dt.date >= di.budat)
where dt.date >= add_months(current_date(), -14) and dt.date <= current_date()
group by dt.date
order by dt.date asc;
Expected output; This table visualization should also slice and dice according to filters applied by user
No need for either M or DAX. Let the data model do the work for you. Something akin to an inner join is achieved by filtering out blanks from both tables.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |