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?

 

 

 

 

  • 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])
    )
    )
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago
      I will get back to you on your questions

      I am out of office now.. On my mobile

      You can share your file with me as well if possible for you
      • misul's avatar
        misul
        Helper I
        8 years ago

        Zubair_Muhammad

        Much appreciated. Can't upload my source files, but I have posted sample fact and lookup tables in images above.

         

        Would be great to have any info on how to achieve this dynamic slicing .. with or without using the following methods.

         

        Topics I have researched:

        a. Dynamic documentation ( https://www.daxpatterns.com/dynamic-segmentation/) - My desired solution seems to match this, but there are no duplicate rows in their sample file, so I couldn't follow this.

        b. SUMMARISE ROLLGROUP function -https://curbal.com/blog/glossary/summarize-dax  ( still figuring out how to use this function)