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

      • misul's avatar
        misul
        Helper I
        8 years ago

        Zubair_Muhammad Just wondering if you could help me out with my  first question.. 

         

        a) what is the idea behind creating a measure in the Lookup table instead of my Fact table? How can this apply the dynamic category when slicing per year and per region?