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
Anonymous
Not applicable

Final client status by grouping on selected date range


Hi,

I am fairly new Power BI user, currently working on interesting piece of DAX logic, which should not cause too much hasle, but I feel like I missing something in my code and cannot find out what exactly.

I have data on client movement through different sales stages which structured like this:

LeadIdIdCreatedDateTimeOldStatusNewStatus
abcabc12018.12.01 12:00:01NewCalling
abcabc22018.12.02 13:30:00CallingProposition
abcabc32018.12.03 10:00:05PropositionRegistration
abcabc42018.12.04 14:44:00RegistrationSale


I have to represent this data by slowing the max status the client has achieved for any selected date range. E.g. if I select 12.02 to 12.03, I would want the matrix table to add a count to Registration counter like this:

Final statusCount
New0
Calling0
Proposition0
Registration1
Sale0

 
Here is the current code that I tried using:

Measure :=
COUNTROWS (
    FILTER (
        'Raw LH',
        GROUPBY (
            FILTER (
                ADDCOLUMNS (
                    SUMMARIZE (
                        CALCULATETABLE (
                            'Raw LH',
                            FILTER ( DateTable, DateTable[Date] >= MIN ( DateTable[Date] ) ),
                            FILTER ( DateTable, DateTable[Date] <= MAX ( DateTable[Date] ) )
                        ),
                        'Raw LH'[LeadId],
                        'Raw LH'[NewStatus],
                        'Raw LH'[CreatedDateTime],
                        'Raw LH'[Id]
                    ),
                    "MaxDate", CALCULATE (
                        MAX ( 'Raw LH'[CreatedDateTIme] ),
                        FILTER ( 'Raw LH', 'Raw LH'[LeadId] = EARLIER ( 'Raw LH'[LeadId] ) )
                    )
                ),
                'Raw LH'[CreatedDateTIme] = [MaxDate]
            ),
            'Raw LH'[Id]
        )
    )
)


In this code, If I understand correctly, I am selecting the data for the selected range, then grouping on the basis of LeadId, interaction Id, InteractionTime and the newStatus. I am also adding another column for the max date. I then select only the rows with max date, and group again by unique event identifier (Id).

As much as I have tested this code, the relative date filtering seems to be working, so does grouping and selecting the latest, thus the maximum, interaction, however when run this measure in the matrix table, it adds count for all interactions during the select time period.

I hope this makes sense and I would greatly appreciate if you could help me with this. I feel like I am so close to getting it right, but as much as try I can't crack this.

Thanks,
Arturs.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello all,

I have found solution to my problem. Here is the code:

 

CountOfMaxStatus = COUNTROWS(
FILTER (
        TestData;
        TestData[TimeStamp]
            = CALCULATE (
                MAX ( TestData[TimeStamp] );
                FILTER(ALL(TestData);TestData[Client] = EARLIER ( TestData[Client]));
                FILTER(ALL(TestData);TestData[TimeStamp]>=MIN(DateTimeDimension[TimeStamp]));
                filter(ALL(TestData);TestData[TimeStamp]<=MAX(DateTimeDimension[TimeStamp]))
            )
))

Here is link to the pbix file if you want to see it in the action: http://www.filedropper.com/maxstatusmeasure

 

Thanks @AlB and @v-lili6-msft for the help.

View solution in original post

19 REPLIES 19
Anonymous
Not applicable

Hello all,

I have found solution to my problem. Here is the code:

 

CountOfMaxStatus = COUNTROWS(
FILTER (
        TestData;
        TestData[TimeStamp]
            = CALCULATE (
                MAX ( TestData[TimeStamp] );
                FILTER(ALL(TestData);TestData[Client] = EARLIER ( TestData[Client]));
                FILTER(ALL(TestData);TestData[TimeStamp]>=MIN(DateTimeDimension[TimeStamp]));
                filter(ALL(TestData);TestData[TimeStamp]<=MAX(DateTimeDimension[TimeStamp]))
            )
))

Here is link to the pbix file if you want to see it in the action: http://www.filedropper.com/maxstatusmeasure

 

Thanks @AlB and @v-lili6-msft for the help.

@Anonymous

Cool. Thanks for sharing.

v-lili6-msft
Community Support
Community Support

hi, @Anonymous

You maybe take a look at these two Quick Measures as I think you want something like them.

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

By the way, for CreatedDateTime is a datetime column with different time,

and Date table is only a datetime column with 12:00:00 AM.

So you‘d better use this formula to create a new CreatedDateTime

New CreatedDateTime = 'Raw LH'[CreatedDateTime].[Date]

then use this new date column for calculation.

 

Hope these can help you.

 

Best Regards,

Lin

 

 

 

 

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

Hi @v-lili6-msft

 

I am quite curious about the

 

'Raw LH'[CreatedDateTime].[Date]

that you show as I had not seen it before. So you can access the components of date-type data with than syntax? Is this syntax used in other ways too or with other data types?  Can I read about it somewhere?

 

Is the above actually a shortcut for:

 

DATE(YEAR(Raw LH'[CreatedDateTime]), 
MONTH(Raw LH'[CreatedDateTime]),
DAY(Raw LH'[CreatedDateTime])
)

Thanks very much

hi, @AlB

Yes, you could use this formula must before create a relationship with a date table. 

, of course, you could use your formula,

DATE(YEAR(Raw LH'[CreatedDateTime]), 
MONTH(Raw LH'[CreatedDateTime]),
DAY(Raw LH'[CreatedDateTime])
)

 

Best Regards,

Lin

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

Thanks @v-lili6-msft

Is thet syntax that you provide, with the dot, only available for dates  or in others cases as well? That's what I was most curious about.

 

AlB
Super User
Super User

Hi @Anonymous

Could you share a sample data model?  It would make things easier

Anonymous
Not applicable

Hi @AlB, I have shared a sample file with you in the DMs.

Thanks @Anonymous

You can always share through a URL in websites like this

so that everyone can see it

  

Anonymous
Not applicable

Thanks for suggestion, I am new here, so don't know the customs yet. Here's the link:

http://www.filedropper.com/exampledata

Hey @Anonymous

For being a "fairly new Power BI user" you have here a pretty scary-looking, big-ass measure, dude Smiley Wink  

I haven't had much time but a couple of things stand out: 

 

1. Although what you do seems logical overall, I believe the code could be done considerably leaner.

2. What is the point of the CALCULATETABLE() as first argument of SUMMARIZE? Why not just 'Raw LH'?

3. What is the point of the GROUPBY? By using it the result of the code you show above will just be a list of Ids

4. How exactly do you go about setting up the matrix with the measure? The issue might be there. (If you can provide the actual Power BI file including the reports rather than just the excel with, it would be useful)

5. The outermost FILTER(), does it not yield an error at execution time? You are passing on a table as filter argument. 

 

Hopefully tomorrow I will have some more time.

Best

 

Anonymous
Not applicable

 

Hi again,

@v-lili6-msftthanks for attempt, but these solutions do not fix the issue.

@AlBwith regards to your questions:
1. I agree that code which I provided is bloated, because I am trying to stitch together measure that is doing its job.

2. Reason for doing CALCULATETABLE() is to filter the selected date range only

3. GROUPBY() was my attempt to isolate unique eventId' s

4. I see what you mean, and this actually might be the root of the problem, please see below.

 

I tried to simplify the task, and ignore the relative date filtering for a while. I created a new table which just lists the unique status change Ids for the whole period, and then created a connection with the main datatable:

Testing = 
SELECTCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                ExampleData,
                ExampleData[LeadId],
                ExampleData[Id],
                ExampleData[CreatedDateTime]
            ),
            "MaxDate", CALCULATE (
                MAX ( ExampleData[CreatedDateTime] ),
                FILTER ( ExampleData, ExampleData[LeadId] = EARLIER ( ExampleData[LeadId] ) )
            )
        ),
        ExampleData[CreatedDateTime] = [MaxDate]
    ),
    "Id", ExampleData[Id]
)

I then created a measure for exactly the same code as above, but wrapped it in the COUNTROWS(). I then created the table of statuses and inserted both the measure and count of Testing[Id] as values. The output for both variants of the same code are different.

differences.PNG

I don't understand how can this be, because the "Count of Id" is showing the correct output, but "Measure" is showing the wrong one, even though the both use exactly the same code! I feel like I am missing something very obvious here, but I don't know what. ;/

Here is link to the model: http://www.filedropper.com/example_20

Hi @Anonymous

 

Try this. If I've understood what you are looking for I think it might work. Set up the matrix as you just explained. You could also use date on a slicer. This measure will not show anything for the Total as it is now because of the SELECTEDVALUE but if it works we can fix that, if necessary, later. 

 

Measure =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            FILTER (
                'Raw LH';
                CALCULATE (
                    MAX ( 'Raw LH'[CreatedDateTime] );
                    ALLEXCEPT ( 'Raw LH'; 'Raw LH'[LeadId] )
                )
                    = 'Raw LH'[CreatedDateTime]
            );
            ALL ( 'Raw LH'[NewStatus] )
        );
        SELECTEDVALUE ( 'Raw LH'[NewStatus] ) = 'Raw LH'[NewStatus]
    )
)

@Anonymous

 

On the measure in my previous post, the innermost FILTER already gets you a table with only the latest NewStatus per LeadID.

The rest is to make it work the way you want it on the matrix. The tricky part there is that placing NewStatus in the rows is affecting the table when you are doing your calculations and you would want it only to take effect at the end. I mean, first you want to select the latest NewStatus for each LeadID in the period without the filter on NewStatus and then, only then, you want the filter on NewStatus to act to count only the rows with the value you are interested in (Rejected, Qualified...)

 

Regarding the date filtering. Why don't you do it with a slicer on Date?? 

It is true and interesting what @v-lili6-msft mentions. Your date column includes relevant time info (not just 00:00:00 as in most cases). I could not create a relationship with a standard date table, I suspect precisely because of that. It is the first time I am coming across this, so best to refer to what @v-lili6-msft is suggesting. What I did was create an additional column with only the date (no time) in 'Raw LH' and create the relationship with 'Date' through that.    

Anonymous
Not applicable

Thanks you @AlB for the take on this challenge! As you wrote, your measure shows the last status only for the whole period, which is the reason why my approach was to start by grouping the values. I am not sure how to solve this tricky selection part though..

 

@Anonymous

Have you tried to use a Date slicer? It should work with that. It seems to be working for me.

Anonymous
Not applicable

@AlBThe measure your provided shows the max status for the whole period. If date slicer de-selects this max interaction, it will disapear and will not get recalculated. Maybe I am bad at explaining what I want to achieve, so let me illustrate - we have a client with 3 interactions:

ClientInteractionIDDateOldStatusNewStatus
xyzxyz101.decAB
xyzxyz203.decBC
xyzxyz305.decCD

If the date slicer has selected 12.01 - 12.05, the max interaction in this range is xyz3 and the respective max status = "D", therefore I need the output to be like this:

MeasureValue
B0
C0
D1

However, now suppose I adjust date slicer to 12.01 - 12.04 The max interaction in this range is xyz2 and the respective max status = "C", therefore I need the output to look like this:

MeasureValue
B0
C1
D0

This is what I am trying to achieve. I think that your measure is close to what I need, but I lack the knowledge to tweak it.

Hey @Anonymous what's up

I haven't yet had time to look in detail at your latest post but what is the status on this? Have you made any progress?

Anonymous
Not applicable

Hi @AlB, I haven't had time to work on this recently. Should I happen to find the solution, I will definitely share it.

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.