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.

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

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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

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

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

    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.