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_CODEPROMOTION_NAMEPROMOTION_TYPE
AI0000048maser er bazar 5 k novbuy-amount-get-item-free
AI0000049maser er bazar 3k novbuy-amount-get-item-free


i have Sales Table that has data as follows

INVOICE_NOBarcodeDISC_TYPESTORE_CODE
325110500007A000255AI0000049100010011
325110500007A000640 100010011
425110600045A000090 100010007
425110600045A000291 100010007
425110600045A000771 100010007
425110600045A000772 100010007
425110600045A000830AI0000048100010007
1025110600026A000250 100010016
1025110600026A000255AI0000049100010016
1025110600026A000393 100010016


Relationship is Sales table DISC_TYPE to Promotion table PROMOTION_CODE  BOTH direction Many to One
as sales table i have INVOICE_NO and STORE_CODE so DISC_TYPE value shows like thos format But and if i select  slicer of INVOICE_NO and STORE_CODE then i can see like this format also

But i want to select PROMOTION_NAME from slicer and want to see as follow

for INVOICE_NO and STORE_CODE into DAX 

 

Main Objective is when customer purchase $3000 or $5000 dollar they will get A000830 item or A000255 item free

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

Want to know other items that customer actually pick

 

BarcodeDISC_TYPEPROMOTION_NAME
A000255AI0000049 
A000640  
A000090  
A000291  
A000771  
A000772  
A000830AI0000048maser er bazar 5 k nov
A000250  
A000255AI0000049maser er bazar 3k nov
A000393  
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]
        )
    )
     
     

6 Replies

  • 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

  • techies's avatar
    techies
    Super User
    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]
        )
    )