Forum Discussion

RichBradwayNRM's avatar
RichBradwayNRM
New Member
30 days ago
Solved

Filtering a table with multiple conditions from multiple switches pulled from another table

I have two tables.

Table 1 is called OrderData and it contains 4 columns - EventDate, EventName, TicketQty, and TicketRevenue.
Table 2 is called BudgetData and it contains the same 4 columns.

I am using the EventDate and EventName fields in the OrderData table in two separate switches.  I am trying to use their selected values to filter the BudgetData table.  This logic is being used in one measure to return the total quantity of tickets in the budget.  The logic is being used in another measure to return the total revenue of tickets in the budget.

I have been able to successfully do this with only one condition.

QtyBudgetTotal =
    CALCULATE(
        sum(BudgetData[TicketQty]),
            TREATAS(VALUES(OrderData[EventDate].[Date]),BudgetData[EventDate].[Date])
    )
 
But I have no clue what to do to add the second condition to also filter on EventName as I cannot use && with a second TREATAS.

Can someone help me?  Thank you so much.




  • Divyaraj_Rathod's avatar
    Divyaraj_Rathod
    30 days ago

    CALCULATE combines multiple filter arguments with AND automatically, so you don't need to combine two TREATAS calls with && - just add the second TREATAS as its own separate argument:

     

    QtyBudgetTotal =

    CALCULATE(

        SUM(BudgetData[TicketQty]),

        TREATAS(VALUES(OrderData[EventDate].[Date]), BudgetData[EventDate].[Date]),

        TREATAS(VALUES(OrderData[EventName]), BudgetData[EventName])

    )

     

    Every filter argument you pass into CALCULATE is intersected (AND'ed) with the others, so this filters BudgetData by both the selected EventDate and EventName at the same time. Apply the same two-TREATAS pattern to your revenue measure using TicketRevenue instead of TicketQty. Hope this helps!

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    30 days ago

    Hi, 

    please try something like below, whether it works.

     

    QtyBudgetTotal =
CALCULATE (
    SUM ( BudgetData[TicketQty] ),
    TREATAS (
        SUMMARIZE ( OrderData, OrderData[EventDate].[Date], OrderData[EventName] ),
        BudgetData[EventDate].[Date],
        BudgetData[EventName]
    )
)
  • Divyaraj_Rathod's avatar
    Divyaraj_Rathod
    Helper II
    30 days ago

    CALCULATE combines multiple filter arguments with AND automatically, so you don't need to combine two TREATAS calls with && - just add the second TREATAS as its own separate argument:

     

    QtyBudgetTotal =

    CALCULATE(

        SUM(BudgetData[TicketQty]),

        TREATAS(VALUES(OrderData[EventDate].[Date]), BudgetData[EventDate].[Date]),

        TREATAS(VALUES(OrderData[EventName]), BudgetData[EventName])

    )

     

    Every filter argument you pass into CALCULATE is intersected (AND'ed) with the others, so this filters BudgetData by both the selected EventDate and EventName at the same time. Apply the same two-TREATAS pattern to your revenue measure using TicketRevenue instead of TicketQty. Hope this helps!

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    25 days ago

    Hi RichBradwayNRM ,

     

    Could you please review the suggestions provided above and let us know if you have any further questions.

     

    Thanks!!