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

    Bronze Contributor
    Posted 10 days ago
    Edited by Beau Arlt 9 days ago
    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
    989-400-7812
    ------------------------------


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

    Top Contributor
    Posted 10 days ago
      |   view attached
    Depends on which method you are using to connect to data.
    Direct query has some limitations on the query editor options.
    https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

    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
    ------------------------------

    Attachment(s)

    xlsx
    Self Join Example.xlsx   8K 1 version


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

    Gold Contributor
    Posted 10 days ago
    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 = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        ALL('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.
    ------------------------------