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

 

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?