Forum Discussion

learner03's avatar
learner03
Post Partisan
4 years ago
Solved

Filter from two columns from different Table

I have a Calender Table which I use to calculate Previous Day.

I need to calculate Number of Document Numbers that were closed with 48 hours in previous day.

For Close within 48 hours, I have created a calculated column with-

Completed in 48 hours =
IF(
'Order'[Time_Taken]<=48,
"Yes", "No"
)
Order Table  
   
Document NoCompleted in 48 HrsCompleted Time
AB123Yes25/10/2021 1:02:00Pm
AC234No25/10/2021  2:05:15PM
Ad345Yes26/10/2021 1:32:33Pm
AE567No26/10/2021 2:03:23Pm

For calculating closed on previous day, I have 

VAR Prev_Day =
CALCULATE(MAX('Calendar'[Date]), FILTER('Calendar', 'Calendar'[Date] < CurrentDate && 'Calendar'[If work day]=1)).
How can I write a measure to get Count Od Document Number where Day is prev_day and Closed in 48 Hrs is "Yes".
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

    learner03 

     

    What does your data model look like? If the Calendar table is related to the Order Table on Completed Date (you'll need to extract Date Only so it doesn't have the Time), then you can use a simple measure, for example:

     

    Measure = CALCULATE( COUNT(Order[Document No]) , Calendar[PreviousDay] = "Yes", Order[Completed in 48 Hrs] = "Yes")

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi， learner03 ；

    Try it.

    Count =
COUNTX (
    FILTER (
        ALL ( 'Order' ),
        [Completed in 48 hours] = "Yes"
            && [Completed Time] = [VAR Prev_Day]
    ),
    [Document No]
)

    If not applicable, can you share more information about your table structure? Or simple files and scenes after removing sensitive information?

     

    Best Regards,
    Community Support Team_ Yalan Wu


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

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    learner03 

     

    What does your data model look like? If the Calendar table is related to the Order Table on Completed Date (you'll need to extract Date Only so it doesn't have the Time), then you can use a simple measure, for example:

     

    Measure = CALCULATE( COUNT(Order[Document No]) , Calendar[PreviousDay] = "Yes", Order[Completed in 48 Hrs] = "Yes")

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi， learner03 ；

    Try it.

    Count =
COUNTX (
    FILTER (
        ALL ( 'Order' ),
        [Completed in 48 hours] = "Yes"
            && [Completed Time] = [VAR Prev_Day]
    ),
    [Document No]
)

    If not applicable, can you share more information about your table structure? Or simple files and scenes after removing sensitive information?

     

    Best Regards,
    Community Support Team_ Yalan Wu


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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi， learner03 ；

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

     

    Best Regards,
    Community Support Team_ Yalan Wu

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