Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Thanks Anonymous

You can always share through a URL in websites like this

so that everyone can see it

  

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hey Anonymous

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

      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's avatar
        Anonymous
        Not applicable
        7 years ago

         

        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.

        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