Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
6 months ago
Solved

hi

Hello,   I have promotion table that has data as follows PROMOTION_CODE PROMOTION_NAME PROMOTION_TYPE AI0000048 maser er bazar 5 k nov buy-amount-get-item-free AI0000049 maser er baz...
Need Help
  • techies's avatar
    techies
    6 months ago

    Hi abc_777 As i understand, you can create an Invoice–Promotion bridge table using only rows where DISC_TYPE is not blank.
    This lets the promotion slicer filter at the invoice level, so all paid and free items from the same invoice are visible.

     

    Invoice Promotion =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                Sales,
                NOT ISBLANK ( Sales[DISC_TYPE] )
            ),
            "INVOICE_NO", Sales[INVOICE_NO],
            "PROMOTION_CODE", Sales[DISC_TYPE]
        )
    )
     
     
rohit1991's avatar
rohit1991
Super User
6 months ago

Hii abc_777 

 

Create a measure-based solution. Keep a single-direction relationship from Promotion[PROMOTION_CODE] to Sales[DISC_TYPE]. Calculate invoice-level spend using ALLEXCEPT(INVOICE_NO, STORE_CODE), then determine the eligible free item with a SWITCH(TRUE()) condition (≥3000, ≥5000). Use LOOKUPVALUE to return the corresponding PROMOTION_NAME. Place Barcode, INVOICE_NO, STORE_CODE in a table and use PROMOTION_NAME as a slicer. This correctly shows all items purchased per invoice along with the free item earned, filtered by the selected promotion.

abc_777's avatar
abc_777
Solution Specialist
6 months ago

 i cant change direction to single. I need measure and cant put upper and lower limit

thx

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    6 months ago

    Hi abc_777 

    please show what you are trying to get with images of model, relationships. visuals slicers etc and clear explanations around them or it is impossible to understand how to help you.

     

    Having a bidirectional filter is not generally a good practice but you might have specific reasons which I kindly ask you to explain always with images and comments

     

    We need to understand the model and your purpose, visually

     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

    • abc_777's avatar
      abc_777
      Solution Specialist
      6 months ago

      FBergamaschi ,

       

      I understand the directional issue. but as this development was done long ago and for some reason i have made BOTH direction so if i change to single then could possibility having issue to other visuals. 

       

      Keeping that BOTH direction on i wanted to see when select PROMOTION_NAME from Filter Slicer.

       

      Expected Output is as follow

      Barcode

      DISC_TYPE

      PROMOTION_NAME

      A000076

      		 

      (blank)

      A000255

      AI0000049

      maser er bazar 3k nov

      A000697

      		 

      (blank)

       

      Main Objective is when customer purchase $3000 or $5000 dollar they will get A000830 item or A000255 item free. These amount could be change or dynamic. ($3000 or $5000)

      and i want to see what OTHER items customer bought spending $3000 and $5000 so they get those item free. 

      Want to know other items that customer actually pick

      thanks