Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Help! Should be quick :] - Coupon Redemption

  • 1.  Help! Should be quick :] - Coupon Redemption

    Gold Contributor
    Posted Sep 11, 2019 04:16 PM
    Edited by Beau Arlt Sep 12, 2019 03:25 PM
    Context: the below table shows purchased coupons (CouponId) and if there is a related coupon as part of the transaction (PurchasedCouponId). If the related coupon gets used, it then gets listed as another transaction under (CouponId) with no related (PurchasedCouponId).

    Goal: My goal is to look at a segment of coupons (like 1) where we issue (2,000) and each have a unique (PurchasedCouponId) and figure out how many of those (PurchasedCouponId)s show back up in (CouponId).

    Is this best done through a Measure or New Column calculation? How could this be done?

    Table: OperationPermission
    couponId   PurchasedCouponId State_
    1 9991 Active
    1 9992 Active
    2 9993 Active
    3 9994 Inactive
    9991 Active
    9992 Active
    4 Active

    Beau Arlt
    Business Analyst
    Holland MI

  • 2.  RE: Help! Should be quick :] - Coupon Redemption

    Top Contributor
    Posted Sep 11, 2019 07:56 PM
      |   view attached
    Depends on which method you are using to connect to data.
    Direct query has some limitations on the query editor options.

    If you are able to use the query editor merge function, I would:
    Create a copy of the table (either duplicate or reference)
    Replace blanks in table2.PurchasedCouponID with 0. (This will help us tell the difference between a blank and a null value).
    Run merge on the two tables, left joining on table1.PurchasedCouponID = table2.CouponID.
    Then add the PurchasedCouponID from the second table.

    This mimics a self join in SQL.
    Attached is a mock up in Excel.

    If you are  not allowed to use the merge function due to DirectQuery/Live Connection restrictions, then you can do this in DAX.
    DAX has join functions, but I think you have to make a calculated table.

    Hope that helps.

    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR


    Self Join Example.xlsx   8K 1 version

  • 3.  RE: Help! Should be quick :] - Coupon Redemption

    Gold Contributor
    Posted Sep 12, 2019 06:03 AM
    Hi @Beau Arlt,

    If I've understood correctly then you want to count how many Purchased CouponIds show up in the CouponId column.  If your table is just called Table then this measure should work:
    _Count Purchased in CouponID = 
        COUNTROWS( 'Table' ),
        TREATAS(VALUES('Table'[PurchasedCouponId]), 'Table'[couponId] )

    It creates a list of [PurchasedCouponId] values, honouring any filters you have in place, uses that to filter the [CouponId] column and counts the number of rows in the resulting table.

    Hope that works for you,​

    Simon Lamb
    IT Consultant, Method Excel Ltd.