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

Taking the Minimum Value of a filtered table with relationships

Dear all,

 

I am creating a visual for a very specific use case, and I cannot find any posts to guide me through - I am also quite new to PowerBI..

 

In my data set I have one table with a list of products, each product having one or many components. (Table 1 in the picture below) - I defined the Component ID in a way that they only relate to on product.

 

The Components have supply and demand numbers per Calendar Week (Table 2).

 

The demand for the components belonging to one product are the same, since customers demand the product as a whole.

The problem is, that for one product, the amount of Supply depends only on the component with the lowest supply --> meaning I would like to get as an outcome a table with only the product, and the minimum number of component supply per Calendar Week.

 

jdhbr_0-1614272961013.png

 

I am a bit lost how to do this best. I could go back to Excel and write a Macro that does that, but I would like to learn how to do it in PowerBI!

 

Thanks a lot for any kind of support!

Cheers!

 

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

Hi, @Anonymous 

According to your description, you want to get the min value of the Supply and Demand group by Week and Product ID, right? I think you can achieve this using Summarize() function in DAX, you can follow my steps:

  1. Create a calculated column in the 'Table 2’ like this:
Product ID = RELATED('Table 1'[Product ID])

v-robertq-msft_0-1614930781403.png

 

  1. Create a calculated table like this:
Outcome =

SUMMARIZE(

    'Table 2',

    [Product ID],[Calendar Week],

    "DemandMin",MIN('Table 2'[Demand]),

    "SupplyMin",MIN('Table 2'[Supply]))

v-robertq-msft_1-1614930781409.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-robertq-msft
Community Support
Community Support

Hi, @Anonymous 

According to your description, you want to get the min value of the Supply and Demand group by Week and Product ID, right? I think you can achieve this using Summarize() function in DAX, you can follow my steps:

  1. Create a calculated column in the 'Table 2’ like this:
Product ID = RELATED('Table 1'[Product ID])

v-robertq-msft_0-1614930781403.png

 

  1. Create a calculated table like this:
Outcome =

SUMMARIZE(

    'Table 2',

    [Product ID],[Calendar Week],

    "DemandMin",MIN('Table 2'[Demand]),

    "SupplyMin",MIN('Table 2'[Supply]))

v-robertq-msft_1-1614930781409.png

 

And you can get what you want.

You can download my test pbix file here

 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PaulDBrown
Community Champion
Community Champion

@Anonymous 

Can you please provide a sample dataset or PBIX (hiding confidential information) and a depiction of the expected outcome?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Hi @PaulDBrown,

 

Thanks for replying!

Is the screenshot with Table1, Table2 and wished outcome not enough? I created this dummy set with less rows, which looks a lot like mine. I cannot upload anything more specific unfortunately..

@Anonymous 
please share this dummy dataset using Onedrive, Google Drive, Dropbox... it avoids all of us having to type in all the data in an excel sheet.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.