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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
naoyixue1
Post Patron
Post Patron

How to calculate the combination of two layers selection in BI

Hey all,

 

I have a problem to create 2 layers' hierarchy calculation ( combination of 2 layer selection).

 

Since we changed the way to calculate the inventory, I have to keep 2 ways calcualated inventorys and related WOS (week of Supply) in the report.

 

When I create the original repot, I added ' inventory' , 'WOS (week of Supply)', and 'Next Supply' in one selection. And based on the selection, I create the measure. So user can choose whatever they want to see in the table. (see below table in BI and related way how I calculated that)

 

naoyixue1_3-1664842403933.png

 

naoyixue1_1-1664841886144.png

 

Right now, organization wants to see both inventory w/ Quality on hold volume and w/o Quality on hold volume. Given that, I'm thinking whether I can apply the similar logic to create that table in BI (see below)

 

Technically, I will have two selections. The calculation should reflect the combination of those two selection('w/o Quality On Hold' ' w/ Quality On Hold assocatied with 'Inventory', 'WOS' and 'Next Supply''.  Just like 2 drill down selections. Firstly, we can select 'w/o Quality on Hold' or 'w/ Quality on Hold', and then select  'Inventory', 'WOS' and 'Next Supply'. I'm not sure how I should develop the measure to combine them together with selectvalue dax...or Can I use the other functions to calculate that combination hierarchys. Any thoughts? Thanks!

 

naoyixue1_4-1664842635777.png

 

 

7 REPLIES 7
naoyixue1
Post Patron
Post Patron

@HoangHugo I mean how I can make sure as long as user select both "w/ and W/o" selection and the measure ' WOS, Inventory and Next Supply), then user can see the number based on those combinations. Thanks! 

naoyixue1
Post Patron
Post Patron

@HoangHugo Sure.  See below. That's the sample of part of fact table, where I calculated the On Q.

 

Originally, my total inventory = On Q + On Hand. But currently, organization wants to see both inventory w/ and w/o On Q. In other words. I need to have two measures to calculate the inventory.

 

Inventory 1 = On Q + On hand (same as before)

Inventory 2 = On Q

 

Given that, i will have two differernt value of the same measure of Week of Supply, which is calculated based on the inventory. 

 

When I previously had one measure of Inventory( I just applied selectvalue to show the data whatever user select Week of Supply or Inventory. but now at the beginning I have two different Inventory and WOS. How can I add something in the report but still keep the same 2nd layer selection (Week of Supply & Inventory) for user to select whatever they want to see. In other words, How can I support user to select the combination w/ On Q assoicated with Inventory or Week of Supply and w/o On Q assoicated with Inventory or Week of Supply. 

 

naoyixue1_1-1664916596565.png

 

Ok, Understood

Creating a slection "w and w/o On Q" is required to user can choose.

Modify a bit your mearsure of invetory

Inventory = 

var select = SELECTEDVALUE(secltion w and w/o)

return

 Inventory = if(select="without On Q", 0, On Q) + On hand
That's it, when user choose "w or w/o ON Q", Invetory will change as well.

@HoangHugo Thanks for your reply. I know that. Since in overall the report, I want to give the option for the user to see inventory, next supply and week of supply. Given that, I already added one selectvalue dax to catch those 3 measures. (see below). User can select each of those three to reflect the number in the table. Based on what you said, I'm not sure whether I should separete the selectevalue firstly. In other words. set the inventory based on 2 conditions (w/ and w/o On Q)

 

and then still apply my original dax to have that combination (Inventory w/ or inventory w/o On Q) 

 

naoyixue1_0-1664996400588.png

naoyixue1_1-1664996494157.png

 

HoangHugo
Solution Specialist
Solution Specialist

Hi

The attribute "w/Quality on hold" and"w/o Quality on hold" is one column in your data table, right? if so, just create a slicer, and put this column into. You can seet "Select All" in Slicer for user.

No, that's the part of my measure. That's why I have to create two measures with or without the Quality on Hold volume.

 

Any thoughts, guys? Thanks!

Ok, but I cannot see which measures related or used "w/Quality on hold" and"w/o Quality on hold". Or how to defined "w/Quality on hold" and"w/o Quality on hold". Or maybe I can not understand your context. Can you show detail?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.