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
AndersDonker
Helper I
Helper I

Getting all subsequent sales after slicer choice

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 

ProductNameProductIDProductGroup
Product A1Group A
Product B2Group A
Product C3Group A
Product D4Group A
Product E5Group B
Product F6Group B
Product G7Group B
Product H8Group B

 

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

ProductIDCustomerIDSequenceSaleDate
1122022-02-01
2132022-03-01
3142022-04-01
4112022-01-01
7152022-05-01
6232022-04-01
7242022-06-01
8212022-01-01
2222022-03-01
6312022-06-01
2322022-07-01
5452022-10-01
8422022-07-01
2412022-06-01
4432022-08-01
1442022-09-01

 

 

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

AndersDonker_1-1663579051126.png

 

 

 

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

 

AndersDonker_0-1663578741217.png

 

 

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

 

 

 

 

 

 

4 ACCEPTED SOLUTIONS
tamerj1
Super User
Super User

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

1.png

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

 

View solution in original post

CNENFRNL
Community Champion
Community Champion

_ = 
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] ) ),
        ""
    )

CNENFRNL_0-1663601109012.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

tamerj1
Super User
Super User

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

1.png2.png

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

View solution in original post

AndersDonker
Helper I
Helper I

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

View solution in original post

12 REPLIES 12
AndersDonker
Helper I
Helper I

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

tamerj1
Super User
Super User

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

1.png2.png

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

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

 

 

 

 

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.

CNENFRNL
Community Champion
Community Champion

_ = 
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] ) ),
        ""
    )

CNENFRNL_0-1663601109012.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.ProductNameProducts.ProductGroupCount
Product AGroup A1
Product CGroup A1
Product DGroup A1
Product EGroup B1
Product FGroup B1
Product GGroup B2
Product HGroup B1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Record_2022_09_20_08_58_55_118.gif

 

PBI solution is way much trickier than you can imagine,

Record_2022_09_20_12_38_30_577.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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? 

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

tamerj1
Super User
Super User

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

1.png

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

 

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.ProductNameProducts.ProductGroupCount
Product AGroup A1
Product CGroup A1
Product DGroup A1
Product EGroup B1
Product FGroup B1
Product GGroup B2
Product HGroup B1

 

 

 

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!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

AndersDonker
Helper I
Helper I

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!

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.

Top Solution Authors