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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
sharong
Helper I
Helper I

Help me in converting this snowflake query into Power BI

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 

 

sharong_0-1697092843258.png

 

@NikhilChenna @SiemdeKort 

 

1 REPLY 1
lbendlin
Super User
Super 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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.