cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdhbr
Regular Visitor

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, @jdhbr 

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, @jdhbr 

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

PaulDBrown
Super User II
Super User II

@jdhbr 

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.






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..

@jdhbr 
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors