Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eliasayy
Impactful Individual
Impactful Individual

Index by group in table visual

hello everyone i have this table below i made below:

eliasayy_1-1673654763592.png

 

 

the behavior of the table is if i select a date on the slicer, it will show from start date till selected date

what i want is to add an index to the table according to the slicer and by product

so desired result is :

Product           Year-Month             Sales Measure            Index
ASep-20101
AOct-2082
ANov-2003
ADec-2054
AJan-2105
BJun-2001
BJul-2002
BAug-2043
BSep-2094
BOct-2065
BNov-2006
BDec-2007
BJan-2108

 

but it should change when i seelct another date from slicer anotehr example :

eliasayy_2-1673654940714.png

 

desired result:

Product              Year-Month            Sales Measure           Index
ASep-20101
AOct-2082
BJun-2001
BJul-2002
BAug-2043
BSep-2094
BOct-2065


Note that index should be by date(Year-Month) sort as in the example shown

is this possibe?
@tamerj1 @FreemanZ 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1673668702306.png

 

 

Jihwan_Kim_0-1673668664398.png

 

Sales on or before slicer: = 
VAR _slicerdate =
    MAX ( 'Slicer table'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        KEEPFILTERS ( 'Calendar'[Date] <= _slicerdate )
    )

 

Index expected result: =
VAR _startdate =
    CALCULATE ( MIN ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _monthyearbyproducttable =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= _startdate )
    )
RETURN
    IF (
        NOT ISBLANK ( [Sales on or before slicer:] )
            && HASONEVALUE ( 'Calendar'[Month-Year] ),
        COUNTROWS (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                _monthyearbyproducttable,
                ORDERBY ( 'Calendar'[Month-Year sort], ASC )
            )
        )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1673668702306.png

 

 

Jihwan_Kim_0-1673668664398.png

 

Sales on or before slicer: = 
VAR _slicerdate =
    MAX ( 'Slicer table'[Date] )
RETURN
    CALCULATE (
        SUM ( Sales[Sales] ),
        KEEPFILTERS ( 'Calendar'[Date] <= _slicerdate )
    )

 

Index expected result: =
VAR _startdate =
    CALCULATE ( MIN ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _monthyearbyproducttable =
    CALCULATETABLE (
        SUMMARIZECOLUMNS ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ),
        FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= _startdate )
    )
RETURN
    IF (
        NOT ISBLANK ( [Sales on or before slicer:] )
            && HASONEVALUE ( 'Calendar'[Month-Year] ),
        COUNTROWS (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                _monthyearbyproducttable,
                ORDERBY ( 'Calendar'[Month-Year sort], ASC )
            )
        )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors