topic Re: Getting all subsequent sales after slicer choice in DAX Commands and Tips

Getting all subsequent sales after slicer choice

AndersDonker — Mon, 19 Sep 2022 09:24:12 GMT

Dear forum,

It will be my first post here after working for over 4 years with PowerBI. I came across a challenge which I can't seem to solve by myself so I could really use your help!

The premis

I have created a very simple (fictive) dataset to define my challenge. The dataset contains two tables:

- Products which is a lookup table

ProductName	ProductID	ProductGroup
Product A	1	Group A
Product B	2	Group A
Product C	3	Group A
Product D	4	Group A
Product E	5	Group B
Product F	6	Group B
Product G	7	Group B
Product H	8	Group B

- Sales is the table where all the sold products are registered per customer

ProductID	CustomerID	Sequence	SaleDate
1	1	2	2022-02-01
2	1	3	2022-03-01
3	1	4	2022-04-01
4	1	1	2022-01-01
7	1	5	2022-05-01
6	2	3	2022-04-01
7	2	4	2022-06-01
8	2	1	2022-01-01
2	2	2	2022-03-01
6	3	1	2022-06-01
2	3	2	2022-07-01
5	4	5	2022-10-01
8	4	2	2022-07-01
2	4	1	2022-06-01
4	4	3	2022-08-01
1	4	4	2022-09-01

Here a PowerBI datamodel view for the relation between the two:

The challenge
I want to have a slicer on productname and once the user has selected a product, i would like to see the subsequent productsales that have been done.

Here is a visualisation of what i am tryng to accomplish:

The multicolored table is the Sales table with three scenarios.

The yellow table is the result of the slicer choice

The green table is the desired output

I have tried a dynamic table and also tried to search for comparing scenario's online but was not succesfull.

Here is the link to the source excel file and pbix file.

PowerBI and Excel file

Thanks in advanced!

With regards,

Anders Donker

Re: Getting all subsequent sales after slicer choice

AndersDonker — Mon, 19 Sep 2022 09:29:33 GMT

One small addition:

I would like to have a dynamic table or a DAX measure that counts the subsequent Sales per productname as i am not sure if a dynamic table would work in a tabular model.

Thanks!

Re: Getting all subsequent sales after slicer choice

tamerj1 — Mon, 19 Sep 2022 13:36:16 GMT

Hi AndersDonker
Is this what you're looking for? Please see attached file.

Count = VAR SelectedProduct = MAXX ( CALCULATETABLE ( VALUES ( Products[ProductID] ), ALLSELECTED ( Products[ProductName] ) ), Products[ProductID] ) RETURN SUMX ( CALCULATETABLE ( SUMMARIZE ( Sales, Products[ProductName], Sales[CustomerID], Sales[Sequence], Sales[SaleDate] ), ALL ( Products[ProductName] ) ), VAR CurrentDate = Sales[SaleDate] VAR CurrentCustomerTable = CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[CustomerID] ) ) VAR T1 = FILTER ( CurrentCustomerTable, Sales[ProductID] = SelectedProduct ) VAR ProductDate = MAXX ( T1, Sales[SaleDate] ) RETURN IF ( CurrentDate > ProductDate, 1 ) )

Re: Getting all subsequent sales after slicer choice

CNENFRNL — Mon, 19 Sep 2022 15:29:54 GMT

_ = VAR __id = CALCULATE( MAX( SALES[CustomerID] ), ALLEXCEPT( SALES, SALES[CustomerID] ) ) VAR __ds = CALCULATETABLE( SUMMARIZE( SALES, SALES[CustomerID], SALES[Sequence] ), ALLEXCEPT( SALES, PROD[ProductName] ) ) VAR __seq = MAXX( FILTER( __ds, SALES[CustomerID] = __id ), SALES[Sequence] ) RETURN IF( NOT ISBLANK( __seq ) && __seq < CALCULATE( MAX( SALES[Sequence] ), ALLEXCEPT( SALES, SALES[Sequence] ) ), "" )

Re: Getting all subsequent sales after slicer choice

AndersDonker — Tue, 20 Sep 2022 07:22:50 GMT

Hi CNENFRNL,

Thanks for the quick answer. You managed to pull off exactly what I asked for, however, my initial questioning was not adequate.

The desired output would be the counts of subsequent products per productname.

So when selecting Product B from the slicer. I would like to see the following:

Count = 8 --> as 8 subsequent products sold.

And i would like to re-use the Products lookup table to give some additional depth to the Subsequent productcount.

So once the dax measure is added to a chart or a table with the addition of ProductGroup and ProductName.

Resulting into the following table:

Products.ProductName	Products.ProductGroup	Count
Product A	Group A	1
Product C	Group A	1
Product D	Group A	1
Product E	Group B	1
Product F	Group B	1
Product G	Group B	2
Product H	Group B	1

Re: Getting all subsequent sales after slicer choice

AndersDonker — Tue, 20 Sep 2022 07:28:33 GMT

Hi tamerj1 i have the same response to your solution as for CNENFRNL.

Thanks for the quick answer. You managed to pull off exactly what I asked for, however, my initial questioning was not adequate.

The desired output would be the counts of subsequent products per productname.

So when selecting Product B from the slicer. I would like to see the following:

Count = 8 --> as 8 subsequent products sold.

And i would like to re-use the Products lookup table to give some additional depth to the Subsequent productcount.

So once the dax measure is added to a chart or a table with the addition of ProductGroup and ProductName.

Resulting into the following table:

Products.ProductName	Products.ProductGroup	Count
Product A	Group A	1
Product C	Group A	1
Product D	Group A	1
Product E	Group B	1
Product F	Group B	1
Product G	Group B	2
Product H	Group B	1

Furthermore, what is the standard policy for accepting the solution? You both managed to provide me exactly what i asked for. Unfortunately my question was not adequate enough. Should i accept both your solutions? Or should i accept the solution once my additional question is answered?

Thanks!

Re: Getting all subsequent sales after slicer choice

CNENFRNL — Tue, 20 Sep 2022 10:40:50 GMT

For fun only, a showcase of powerful Excel worksheet formulas,

PBI solution is way much trickier than you can imagine,

Re: Getting all subsequent sales after slicer choice

AndersDonker — Tue, 20 Sep 2022 12:35:23 GMT

Your PowerBI solution seems to do what i had in mind and i was fully aware that it would be quite complex! thats why i posted my first forum question!

Could you also upload the PowerBI solution so i can review?

Re: Getting all subsequent sales after slicer choice

tamerj1 — Tue, 20 Sep 2022 12:45:11 GMT

Hi AndersDonker
Here is another solution I guess as per your requirement. Using a slicer table.

Count = VAR SelectedProduct = MAX ( 'Product Slicer'[ProductID] ) VAR FilteredTable = FILTER ( ALL ( Sales ), Sales[ProductID] = SelectedProduct ) VAR Result = SUMX ( Sales, VAR CurrentCustomer = Sales[CustomerID] VAR SelectedDate = MAXX ( FILTER ( FilteredTable, Sales[CustomerID] = CurrentCustomer ), Sales[SaleDate] ) RETURN IF ( Sales[SaleDate] > COALESCE ( SelectedDate, TODAY ( ) ), 1 ) ) RETURN Result

Re: Getting all subsequent sales after slicer choice

CNENFRNL — Tue, 20 Sep 2022 13:20:31 GMT

pbix file is uploaded now. Enjoy such a brain teaser, enjoy DAX!

Re: Getting all subsequent sales after slicer choice

AndersDonker — Thu, 22 Sep 2022 07:00:01 GMT

You expert users are exceptional in your speed of response and explanations!

My compliments for the results. I have received two solutions for a single problem. Thanks! I will accept both solutions.

With regards,

Anders Donker

Re: Getting all subsequent sales after slicer choice

AndersDonker — Fri, 23 Sep 2022 08:47:14 GMT

Hi tamerj1 , i decided to go forward with your solution as it gives me the most freedom to reuse the dimensions in the Products table as in the real world i have plenty of dimensions in the Products table which endusers wish to filter upon.

I can follow along your solution quite well until i reach the final if statement with the coalesce. Can you elaborate why you are using coalesce with TODAY() and why it is needed? I have trouble comprehending why it's needed. If i remove it from the final solution i get very unexpected results but don't understand why.

Also, i plan to reuse this measure to calculate the difference in months between the product selected in the slicer and the results produced for subsequent products sold. Any suggestions on how I could reuse this?

My plan is to add a slicer with a numerical input between 1 and 12 to give the endusers the freedom to limit the period of subsequent sales. My guess is that the TRUE result of the IF statement, the 1, could be replaced with a datediff function to determine the datediff in months for every row i will have half of my challenge solved. What are your thoughts about that?

With regards and much respect for the given solution (!!),

Anders Donker

Re: Getting all subsequent sales after slicer choice

tamerj1 — Fri, 23 Sep 2022 09:50:52 GMT

Hi AndersDonker
Regarding the usage of COALESCE

VAR SelectedDate =
MAXX (
FILTER ( FilteredTable, Sales[CustomerID] = CurrentCustomer ),
Sales[SaleDate]
)

If the table which MAXX iterates over is empty then the valiable value would be blank.

IF (
Sales[SaleDate] > COALESCE ( SelectedDate, TODAY ( ) ),
1
)

Now if the value is blank then the IF condition will return TRUE as the Sales[SaleDate] will always be greater than blank. Therefore I return Today being greater than the greatest available date in the sales table in order to force FALSE over this condition as selected date do not actually exist. You need to imagine that.

For the other part please post a new question with all the details including example of desired results.