Forum Discussion

misul's avatar
misul
Helper I
8 years ago
Solved

Dynamic Segmentation - with duplicate rows

Hi, 

I am trying to follow this blog post about Dynamic Segmentation. https://www.daxpatterns.com/dynamic-segmentation/

 

I am stuck on one point - what to do when there are duplicate transactions? 

 

 

As you can see in the image, the category applied to Product A is not correct. 

 

Any help would be much appreciated. Thank you!

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    misul

     

    I have used this MEASURE in the LookUp Table

     

    Measure =
VAR mysales =
    CALCULATE ( SUM ( Table1[Sales] ) )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            VALUES ( Table1[Product] ),
            mysales >= SELECTEDVALUE ( CategoryLookup[Lower] )
                && mysales < SELECTEDVALUE ( categoryLookup[Upper] )
        )
    )

     

19 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI misul

     

    Please Try this revision. Just summing the sales for product before determining category

     

    Applied Category =
VAR Mysales =
    CALCULATE ( SUM ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Product] ) )
RETURN
    CALCULATE (
        VALUES ( CategoryLookup[Description] ),
        FILTER (
            CategoryLookUp,
            MySales >= CategoryLookup[Lower]
                && Mysales < categoryLookup[Upper]
        )
    )

     

     

    • misul's avatar
      misul
      Helper I
      8 years ago

      Many thanks for the quick reply Zubair_Muhammad. Now I can slice by the AppliedCategory and so it solves my first issue.

       

      The next step for me is to "dynamically change" the applied category when any other slicer is applied (such as Year or Region).  

       

      As you can see in the image below, when I select the year 2016, the applied categories are not correct anymore. I am trying to follow the link from DAX patterns but I am not sure I am going down the right road. 

       

       

       

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi misul

         

        Then you will need to use a MEASURE....becasue calculated columns are not dynamic

         

        I have not tested it but try this MEASURE in the CategoryLookup Table

         

        Measure =
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        Table1,
        Table1[Sales] >= CategoryLookup[Lower]
            && Table1[Sales] < categoryLookup[Upper]
    )
)