Solved: Re: Using a virtual table to filter another table ... - Page 2

SlavaSha7 · ‎12-28-2020

Hello dears,

I have a sales table with fields:

* Date

* ProductID

* Revenue

and a Stocks table with fields:

* Stock Date

* ProductID

* InStock_Quantity

I need to calculate year revenue of products that in stock by day, for example,

Date	Revenue In Stock	Revenue Out of Stock
12/01/2020	0.7M	0.3M
12/02/2020	0.69M	0.31M
12/03/2020	0.8M	0.2M

i.e. the total revenue of 2020 year is 1M, on 1st Dec we had some products that made 0.7M in our yearly revenue. We had another set of products on the 2th Dec that made 0.69M in our yearly revenue.

I have created a virtual table to get stocks by products by day

var TabStock = 
SUMMARIZE(
    Stocks,
    Stocks[ProductId],
    "Stock_QTY",
    MAXX(
        Filter(Stocks,
        Stocks[Stock Date] = MAXX(
                        FILTER(
                            Stocks,
                            Stocks[Stock Date] <= max('Date'[Date])
                            ),
                        [Stock Date]
                        )
                ), 
        Stocks[Max In Stock])
    )

Now I would like to filter the Sales table by products that are in stock using the above virtual table, however I cannot write a right DAX expression for IN condition. Perhaps my issue is I must create a physical table and it's not possible to use a virtual table in the expression to filter anoter table.

So my result query is:

return
Calculate(
    SUMX(
        FILTER(Sales,
            Sales[ProductId] in VALUES(
                                                Filter(Allexcept(
                                                    TabStock, 
                                                    [ProductID]), 
                                                    TabStock[Stock_QTY] > 0
                                                )
                                                )
        ),
    Sales[Revenue]
    )
    )

I cannot work out with the right part of the IN section or perhaps I should use completely another approach.

If simplify, my measure should be like this:

Select
  Date,
  sum(revenue) as RevenueInStock
from Sales S
Where S.ProductID in
      (
        select distinct(S.ProductID) from Stocks S where S.Stock_Quantity > 0
      )

Thank you for advance.

Best regards,

Slava

SlavaSha7 · ‎01-05-2021

hello dear @littlemojopuppy ,

Thank you very much for your helping and efforts! I really appreciate this!

This formula doesn't work because it displayes the same value on each day.

However, I was able to workout with subquery and it works as expected now.

So, how it works?

I need to summarize all sales for all periods but only for Products that are in stock on a specific day

Grand total revenue in stock only = 

Calculate(
SUMX(
  Filter(
  All(Sales),
  Sales[ProductId] in <PRODUCTS IN STOCK ON THIS DAY>
  )
 )
)

"PRODUCTS IN STOCK ON THIS DAY" - is a one column table or a subquery.

To create this subquery I use SUMMARIZE function and it works good:

  var TabStock =
      SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            )

The problem is SUMMARIZE returns a table with two columns (ProductID and Stock_QTY) and that is why the table cannot be used in "IN" clouse.

To improve this I have to make a one column table from the two columns table. To do this I use SELECTCOLUMNS function:

var d = MAX('Date'[Date])

var TabStock = 
SELECTCOLUMNS(
    FILTER(
        //tab creation section begin
        SUMMARIZE(
            Stocks,
            Stocks[ProductId],
            "Stock_QTY",
            MAXX(
                Filter(Stocks,
                Stocks[Date] = MAXX(
                                FILTER(
                                    Stocks,
                                    Stocks[Date] <= d
                                    ),
                                Stocks[Date]
                                )
                        ), 
                Stocks[Stocks])
            ),
            //tab creation section END
        [Stock_QTY] > 0
        ),
    "ProductId",
    [ProductId]
)

Now my measure with the subquery should work:

Thanks a lot for your assistens and time!

Best regards,

Slava.

View solution in original post

littlemojopuppy · ‎01-03-2021

Is this your homework??? 🤔

SlavaSha7 · ‎01-03-2021

@littlemojopuppy no 🙂

I have a big PBX file with ~2M rows and to simplify my queries I have created a very simple file then I am going to copy formulas to my real file.

littlemojopuppy · ‎12-28-2020

Hi! Try this instead...

VAR	InventoryInStock =
	FILTER(
		ALLEXCEPT(
			TabStock, 
			[ProductID]
		), 
		TabStock[Stock_QTY] > 0
	)
RETURN
				
CALCULATE(
	[Total Sales],
	InventoryInStock
)