Forum Discussion

saddas's avatar
saddas
Frequent Visitor
4 years ago
Solved

Count based on date in another table

I have two tables that represent product orders and returns:

Orders: Order ID, Year

Returns: Order ID, Returned

See below image for sample data.

I would like to ceate a visual that shows the ratio of product returns by order year. According to the sample data, the graph would show two data points: 2/3 for 2020 and 1/3 for 2021.

What should be the DAX code for my measure? I tried this: Return Rate = COUNT(Returns[Order ID]) / COUNT(Orders[Order ID])

However, this obviosuly doesn't work when the count is disaggregated by year. 

Thanks a lot.

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    saddas  create  a year column in Orders and use that as axis and rewrite measure like this

     

    Return Rate = 
VAR _num =
    CALCULATE (
        DISTINCTCOUNT ( Orders[Order ID] ),
        Orders[Order ID] IN VALUES ( Returns[Order ID] )
    )
VAR _deno =
    CALCULATE ( 
        DISTINCTCOUNT ( Orders[Order ID] ), 
        ALLEXCEPT ( Orders, Orders[Year] ) )
RETURN
    DIVIDE ( _num, _deno )

     

     

     

8 Replies

  • David-Ganor's avatar
    David-Ganor
    Resolver II
    4 years ago

    Hi saddas , 

    You should create relationship between those two tables using the Order Id columns - and it should work.

    I would recommend using the DIVIDE function instead of "/".

    Hope it helps.

     

    • saddas's avatar
      saddas
      Frequent Visitor
      4 years ago

      Thanks, but I already have a relationship between the two tables. It's actually a many-to-one relationship because the Orders table has an additional column (the PK), which is Row ID. In other words, Order ID in the Orders table is not unique (1 order can contain multiple rows, e.g., if a customer orders two items i na single order.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    saddas  use this measure. works with or without relationship

    Measure2 =
VAR _num =
    CALCULATE (
        COUNT ( Orders[OrderID] ),
        Orders[OrderID] IN VALUES ( Returns[OrderID] )
    )
VAR _deno =
    CALCULATE ( COUNT ( Orders[OrderID] ), ALLEXCEPT ( Orders, Orders[Year] ) )
RETURN
    DIVIDE ( _num, _deno )

     

    • saddas's avatar
      saddas
      Frequent Visitor
      4 years ago

      Hi smpa01 

      Thanks, I think this formula gets close, but it still doesn't get correct values when the returns are disaggregated by date. As per my sample data, the return rate should be 0.67 in 2020 (products 1, 2, 3 are ordered and 1, 3 returned) and 0.33 in 2021 (products 4, 5, 6 are ordered and 4 is returned) . After applying your formula and adding a line visual, the rates are 0.40 in 2020 and 0.10 in 2021.

      You can see the PBIX file here: https://www.dropbox.com/sh/9g6uvptyzazfl0u/AAAsElP-PuQxtnJOHCa-BGLWa?dl=0 

      • smpa01's avatar
        smpa01
        Community Champion
        4 years ago

        saddas  create  a year column in Orders and use that as axis and rewrite measure like this

         

        Return Rate = 
VAR _num =
    CALCULATE (
        DISTINCTCOUNT ( Orders[Order ID] ),
        Orders[Order ID] IN VALUES ( Returns[Order ID] )
    )
VAR _deno =
    CALCULATE ( 
        DISTINCTCOUNT ( Orders[Order ID] ), 
        ALLEXCEPT ( Orders, Orders[Year] ) )
RETURN
    DIVIDE ( _num, _deno )