DAX Measure Help - Filtering by Measure Results

[Thigs]

Hi all, 

First off, thanks for reading and for any help! It is greatly appreciated!

I have a dataset that has individual orders - date, order number, SKU number, and quantity. Each order can have more than one SKU, each SKU can be in more than one order, and each date has multiple orders. 

Now - I have made a table visual with the date and SKU and sum of quantity - ignoring orders for now, we just want to know how much of each SKU was shipped per day. Then I created a new measure checking if the quantity per day is greater than 50% of a pallet - each SKU has a different pallet size, which is located in a second table. 

All of this has gone well. But now I am trying to create a second table - this second table will have a list of dates (365 days/365 rows). Each row will have columns of Count of SKU, Count of Order, Sum of Quantity. But I want to only look at SKUs where in the first table visual we have less than half a pallet (HalfPallet = "True"). 

I can't create a new summary table as it says there is not enough memory - it's a pretty large table. Is there a way to do this whole thing within a measure or two?

Here is the Table #1 - Date Only is my date. SKU Num is my SKU Number, quantity is the sum of how much I shipped of that SKU on that date. Pallet Size is the pallet size (in items) from the fact table. HalfPallet returns true or false - did we ship less than half a pallet that day (if < half a pallet, then HalfPallet = True). Target is the number I am trying to return. It is the total sum of quantity for that date, ignoring SKUs and Orders. I just need a second table with Date and Target but filtered on HalfPallet = True. 

[v-jianboli-msft]

Hi @Thigs ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @Thigs ,

Please try:

Target2 = CALCULATE(SUM('Table'[quantity]),FILTER(ALL('Table'),[Date]=MAX('Table'[Date])&&[quantity]<0.5*[Pallet size]))

Output:

Best Regards,

Jianbo Li

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

[PurpleGate]

Could you add the HalfPallet measure to the second table visual and filter it as "True" there? 

If there is a table relation between your date table and your main table, you should be able to create a second table visual.

HalfPalletMEasure = IF(SELECTEDVALUE(MainTable[HalfPallet])=True,1,0)

[Thigs]

Thanks for the reply! That doesn't seem to work as it gets rid of the SKU and leaves everything blank. 

[PurpleGate]

If you haven't solved it yet, is it possible to get a dummy set of data to see where the problem is?

Maybe there is a solution using Power Query instead.