DAX - Two Months Same customer count - Part 2

DAX DAX Calculations
1

Hi @BrianJ -
Based on the solution provided for Two month same customer i am stuck with another scenario wherein , i need to get the Customer list of the those distinct count of bundle plans selected.
for example if the count is 2 for a particular bundle taken up by the user this month , it should give me a list of those two customers as well in a separate column

2

@Vishy,

While the approach we discussed prior of calling a virtual table via LOOKUPVALUE through ADDCOLUMNS would have worked fine, I found a more direct route - filtering the IntTablePlusMaxPlan virtual table by the evaluation context in the visual, and then iterating CONCATENATEX over the filtered virtual table to extract a comma-delimited string of Customer Names for each row of the visual. The measure used is the exact same as above through the point of calculating IntTablePlusMaxPlan. After that, the remaining code is replaced by this:

VAR ReturnCustList =
    CONCATENATEX(
        FILTER(
            IntTablePlusMaxPlan,
            [@MaxPlan] = SELECTEDVALUE( 'Disconnected Unfiltered DimBundles'[Plan] )
        ),
        [@DistinctCustLM],
        ", ",
        [@DistinctCustLM],
        ASC
    )          
    
RETURN
ReturnCustList

Here are the results for 25MB, 50MB and 100MB to show that this is working properly

image
image728Ă—379 11.5 KB

image
image711Ă—400 10.9 KB

image
image718Ă—372 12.1 KB

I hope this is helpful. Super fun problem. Full revised solution file attached below.

1 Like
3

Hi @BrianJ -
Thanks for the above solution i will try in my production pbix and update you. Yesterday i was playing around with Concatenate but was not understanding how to get the current visual context . Thanks for this!!
Have created new topic and marked you in the same.

4

@Vishy,

Thanks – please let me know how it goes with the production data.

Per your earlier comment, the measure needs to be built around CONCATENATEX, rather than CONCATENATE because the number of customers associated with a given row could be one, two or many. CONCATENATE requires a set number of arguments, while CONCATENATEX just iterates through the specified table stringing the customer names together until it hits the end of the list for each row. In addition, because the table we’re iterating on is virtual, it needs the “X” version of the function to call it directly.

  • Brian
5

@BrianJ - Apologies, i meant in a generalized term as Concatenate :slight_smile: I was working with ConcatenateX itself.

6

Hi @BrianJ,

So while i was testing the customer list fix, i came with a scenario wherein let us take a example customer H had bought two packs in previous month then based on the selection on the slicer , customer H appears twice based on the slicer selection i.e. in 50 mb and also in 100 mb. where as customer H was actually part of 100 MB club because it is maximum that he bough in previous month and the further logic is correct wherein in current month we see the maximum plan a customer took and count accordingly. Which means we need to also get the max plan a customer was in previous month as well and then do the intersect.
Screen shot attached -

Capture1
Capture1712Ă—333 7.16 KB

Capture2
To fix this i included a logic as follows -
VAR PastListLastMo =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
Data,
Data[Customer Number]
),
“@MaxPlan1”,[Max Plan Purchased Prev]),
Data[Month Yr] = LastMoInCal
)
VAR CustListLastMo =
FILTER(PastListLastMo,[@MaxPlan1] = SelPlan)

With this in place the out is correct till IntTablePlusMaxPlan but post that things show blank.

I have also created a measure - Max Plan previous month =
Max Plan Purchased Prev =
VAR LastMOnth = CALCULATE(MAX(Dates[MonthInCalendar]),DATEADD(Dates[Date],-1,MONTH))
VAR MaxIndex =
CALCULATE(
MAX( Data[Plan Index] ),
FILTER(
ALL( Dates ),
Dates[MonthInCalendar] = LastMOnth
)
)

VAR MaxPlan =
LOOKUPVALUE(
DimBundles[Plan],
DimBundles[Plan Index],
MaxIndex
)

RETURN
MaxPlan

7

@Vishy,

Hmmm…that’s an interesting curveball that I hadn’t considered prior. Before I jump back into revising this measure, which given its length and complexity is a significant undertaking, I want to be sure we’ve considered all the other variants of this case and have clear business rules on how to handle them in the measure.

For example, what if in March, a user buys two plans - 100MB and 200MB, but in April consolidates down to one plan of 150MB. Is this considered an upgrade from 100MB (green icon) or a downgrade from 200MB (red icon)? What about if they upgrade to one 500MB plan - Does the 200MB entry get “credit” for the upgrade and the 100MB entry falls off the list?

Just want to make sure we’ve defined the full range of possible situations before doing a revision, so hopefully we will only have to do it once.

Thanks.

  • Brian
8

Hi BrianJ,

Very valid point , let me come back to you on this , the business scenario what i mentioned above is something i observed, let me wait till next Monday to understand what is user feedback on this if there are more inputs we can incorporate them accordingly rather than reworking on this again and again.

1 Like