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...
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] )
        )
    )

     

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]
    )
)
    • misul's avatar
      misul
      Helper I
      8 years ago

      Zubair_Muhammad You are right, I would need to use a measure... I tested the formula and gives the following error. 

       

      But I have two follow-up questions : a) what is the idea behind creating a measure in the Lookup table instead of my Fact table?

       

      b) Would it be possible to modify the initial formula for a calculated column "AppliedCategory" by asking it to sum up the sales values for a given year or region?

       

       

       

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago
        Sorry I forgot it's a measure
        Here is the revised measure


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