Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Measure

I have Project Table, some necessary columns are given below:

Project CaseOrganizationStartPeriodEndPeriod
42251XYZ8/27/201710/16/2017

 

I have another table Tickets, which looks like below:

CaseOrganizationStartPeriodEndPeriod
52454XYZ8/29/20179/10/2017
56464XYZ11/16/201712/12/2017
54859XYZ11/19/201712/12/2017
52421XYZ1/9/20182/2/2018


I want to create separate measures for the below:
1) Case in Tickets table created between the StartPeriod and EndPeriod of Project Case.
2) Case in Tickets table create during the first 30 days after the EndPeriod of Project Case.

Any help 

 

General Comment
Need Help
Tips and Tricks
  • smpa01's avatar
    smpa01
    4 years ago

    Anonymous  so as I understand, Measure1 is working as intended but Measure2 is not with the revised dataset.

     

    Try this out

     

    Measure2 =
VAR _0 =
    CALCULATE (
        MAX ( Project[EndPeriod] ),
        FILTER (
            VALUES ( Project[Organization] ),
            Project[Organization] = CALCULATE ( MAX ( Tickets[Organization] ) )
        )
    )
VAR _1 =
    CALCULATE (
        COUNT ( Tickets[Case] ),
        FILTER ( Tickets, Tickets[StartPeriod] > _0 && Tickets[StartPeriod] <= _0 + 30 )
    )
RETURN
    _1

     

     

6 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous  so as I understand, Measure1 is working as intended but Measure2 is not with the revised dataset.

     

    Try this out

     

    Measure2 =
VAR _0 =
    CALCULATE (
        MAX ( Project[EndPeriod] ),
        FILTER (
            VALUES ( Project[Organization] ),
            Project[Organization] = CALCULATE ( MAX ( Tickets[Organization] ) )
        )
    )
VAR _1 =
    CALCULATE (
        COUNT ( Tickets[Case] ),
        FILTER ( Tickets, Tickets[StartPeriod] > _0 && Tickets[StartPeriod] <= _0 + 30 )
    )
RETURN
    _1

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      smpa01 This worked well! Thanks
      Instead of Count I just used Distinctcount, as there were duplicates in my data. Just wanted to update if anyone refers to this solution.

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

     

    Measure1 = 
CALCULATE (
    COUNT ( Tickets[Case] ),
    FILTER (
        Tickets,
        Tickets[StartPeriod] >= MAX ( Project[StartPeriod] )
            && Tickets[StartPeriod] <= MAX ( Project[EndPeriod] )
    )
)

Measure2 = 
CALCULATE (
    COUNT ( Tickets[Case] ),
    FILTER (
        Tickets,
         Tickets[StartPeriod] <= MAX ( Project[EndPeriod] )+30
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      smpa01 For Measure 2, I am not getting correct results.
      Every Organization Starts with a Project Case, it may last upto N number of days depending upon how long it took.
      But I need to know the Cases submitted in the First 30 days when the Project Case got Closed(EndDate)
      For Ex: If Project Case took the Entire August Month to complete and get Closed(8/31/2021). I need to know the Cases submitted in the Month of September (30 days).

      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous  my solutuion was based on the data provided.

         

        Improve your question with better sample data if you are not getting the result you desire.