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
Anonymous
Not applicable

SQL Case Nested

Hello,

 

So I have some logic in different places, and I'm trying to bring it into one case statement in SQL. This is a little new to me, but I thought this may be optimal versus having a bunch of calculated columns.

 

Below is an example of my current query:

select
pos.quantity_on_hand,
d.cal_date,
case
when date_key in( Select max(date_key) from edw.D_dates group by fiscal_year_nbr, fiscal_week_nbr)
then 'Y'
else 'N'
end Week_end_flag
from
edw.f_pos_daily pos,
edw.d_dates d,
where
pos.pos_date_key = d.date_key

 

The "problem", is I then need to use these "flags" that I've defined below and I create a column in Power BI that returns the MAX d.cal_date from edw.d_dates where the flag is a "Y".  

 

This is what I used for my column calculation:

Last Inventory Date = RETURN(CALCULATE(MAXX(Inventory, Inventory[Cal_date]), filter ('D_Dates', 'D_Dates'[Week_end_flag]="Y")).

 

Again, I'm looking for a way to combine these together.  I don't necessarily NEED the Week_End_Flag as a column. It's the last date that I really care about.

 

To visualize it, column D is what I'm going for here:

 
 
 
Quantity On HandCal_DateFlagLast Inventory Date
3501/18/2021N1/21/2021
3351/19/2021N1/21/2021
3201/20/2021N1/21/2021
4001/21/2021Y1/21/2021
3801/22/2021N1/21/2021
3701/23/2021N1/21/2021
    

 

TIA!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you so much for your response Tom!

 

So I am using an import to pull in the data from an Oracle Database.

 

You could consider the data is organized into two data imports.

1. One is an inventory table that contains quantity, customer name, item number, and store number.

2. I also have a date table that I am pulling in.  Where it gets a little complex is that I am looking at data for 7 different customers who all have their own calendar year/weeks.  This is called out in the date table, for example, there is a column that states "Walmart Fiscal Year, Walmart Fiscal Week".

 

So initially, I created those flags on my dates table (one for each customer) to flag their end of week with a "Y".  I then created a column in Power BI on the inventory table, to return the max date that has a flag (and has inventory data). So this essentially would give me the max date (less than todays date) that has a "Y" flag.  

 

In the end, I'm after this question: "What is the current inventory for this customer?"  So, to get here, I then created a calculated column that sums the quantity on hand for that last inventory date.

 

Again, all of this works fine, but I just feel like it's so many steps, and maybe there is a way to eliminate some of them to make my report run a little more efficiently. 

 

Future, I would also like to be able to see inventory for the same customer fiscal week for last year. I'm not quite ready to tackle this one yet though. 😉  

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Thank you so much for your response Tom!

 

So I am using an import to pull in the data from an Oracle Database.

 

You could consider the data is organized into two data imports.

1. One is an inventory table that contains quantity, customer name, item number, and store number.

2. I also have a date table that I am pulling in.  Where it gets a little complex is that I am looking at data for 7 different customers who all have their own calendar year/weeks.  This is called out in the date table, for example, there is a column that states "Walmart Fiscal Year, Walmart Fiscal Week".

 

So initially, I created those flags on my dates table (one for each customer) to flag their end of week with a "Y".  I then created a column in Power BI on the inventory table, to return the max date that has a flag (and has inventory data). So this essentially would give me the max date (less than todays date) that has a "Y" flag.  

 

In the end, I'm after this question: "What is the current inventory for this customer?"  So, to get here, I then created a calculated column that sums the quantity on hand for that last inventory date.

 

Again, all of this works fine, but I just feel like it's so many steps, and maybe there is a way to eliminate some of them to make my report run a little more efficiently. 

 

Future, I would also like to be able to see inventory for the same customer fiscal week for last year. I'm not quite ready to tackle this one yet though. 😉  

TomMartens
Super User
Super User

Hey @Anonymous ,

 

I have to admit that I do not understand what you want to create, can you please clarify this.

My question(s) is about the nature of your Power BI dataset, are you connecting to the underlying source data using direct query or are going to import data from the source data.

 

I do not understand why you want/have to brig the logic to the SQL statement

 

Assuming you are going to import the data, then you can create a dedicated calendar table inside your Power BI data model.

Inside this table you can create columns that contain the values for start date and end date of a week, this is described here: Solved: Re: Week commencing in DAX - Microsoft Power BI Community

 

After a relationship is established between your calendar table and your fact table you can easily create the column Week_End_Flag by using a DAX like this
Week_End_Flag = RELATED( 'Calendar'[EoWDate].

To be honest at the current moment, I can't say if the DAX creates a calculated column or is used to compute a virtual column inside a more complex measure.

 

This is because I do not understand the nature/meaning of Last the /relationship of the tables used for the final statement.

Maybe you want to clarify this.

 

Maybe this article Design a data model in Power BI  - Learn | Microsoft Docs will help to finally decide if you bring down the DAX statement to the SQL statement or the other way round.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.