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
Anonymous
Not applicable

Filter by or aggregation ? How to filter by a field but show more

Hi ! 

I really need your help cause I don't know how to resolve my question (if it's on measure or directly on data model). 
I've got a complex data model. I tried to do a really simple example to explain. 

 

I have NOSeller. 

I have Name Seller. 

I have concatenate them NOSeller-Name. => this is my filter on dashboard.

I have CA for each one

I have month year as date field

 

BUT a Name Seller can change and the NOSeller is attribute to another person. 

For example :

In september 2017 : 1259-tom 

In februar 2018 : 1259-guth

 

Guth take back tom's results.

 

 

When I do my research, they want to select 1259-guth, they want to see its results from january (ok, I did it with totalytd) BUT they also want to see its results from last year. Even if it was tom, they don't care, it's like it is to him now. 

 

But with my formula : 

Cumul A-1 CA =
CALCULATE(TOTALYTD([CA]; DIM_TEMPS[MONTHYEAR]);PARALLELPERIOD(DIM_TEMPS[MONTHYEAR]);-12;MONTH))
 
It's not working because it doesn't take in count the NOSELLER because I filtered on name and number. 
 
 
FOR EXAMPLE : 

Capture.PNG

If I select "1259-guth", for last year, it will give me nothing but I would like to see "1200" for last year. 
Actually, I want to agg the measure with the NOSELLER

 
 
How can I do to "force" the measure to calculate with the NOSELLER even if my filter is on the concatenate field ? 


Thanks by advance, 
4 REPLIES 4
d_gosbell
Super User
Super User

I'm not sure I completely understand what you are doing with the TOTALYTD function. But if you wanted to look up the NOSeller value and return the sum of the CA column you could do something like the following:

 

CA by NOSeller =
VAR _noSellerName = SELECTEDVALUE( DIM_TEMPS[NOSeller-Name] )

VAR _noSeller = LOOKUPVALUE( DIM_TEMPS[NOSeller]; DIM_TEMPS[NOSeller-Name]; _noSellerName ) 

CALCULATE( SUM( DIM_TEMPS[CA] ;
    ALL( DIM_TEMPS[NOSeller-Name] ) ;

    DIM_TEMPS[NOSeller] = _noSeller
)

Actually I was just thinking about this a bit more and I think we can simplify this even further to something like the following:

 

CA by NOSeller =
CALCULATE( SUM( DIM_TEMPS[CA] ;
    ALL( DIM_TEMPS[NOSeller-Name] ) ;

    VALUES( DIM_TEMPS[NOSeller] )
)

Anonymous
Not applicable

Hi ! 

Thanks ! 

But I can't use the "all function" because I really need to filter with it. 

I don't know how to "force" the filter to use another dimension for its measure


@Anonymous wrote:

Hi ! 

Thanks ! 

But I can't use the "all function" because I really need to filter with it. 

I don't know how to "force" the filter to use another dimension for its measure


Have you even tried the expression? Because you can't force the filter to use another column if you don't first use ALL to remove the current filter.

 

Isn't the behaviour in the highlighted cells below something like what you are after?

 

2019-07 sum NOSeller.png

In the "Current" version of the calculate I added a check to see if the NOSeller is the person with the latest YearMonth value by adding a column called IsCurrentNOSeller

 

IsCurrentNoSeller = 'Table'[MonthYear] = maxx( Filter('Table', 'Table'[NOSeller] = EARLIER('Table'[NOSeller])),'Table'[MonthYear])

The just wrapping the existing expression in a check to see if the selected NOSeller-Name was the latest one. (and if there is not a single value for IsCurrentNoSeller I return true so that the totals still calcualte correctly)

CA by Current NOSeller = IF(SELECTEDVALUE('Table'[IsCurrentNoSeller],TRUE()),
    CALCULATE(SUM('Table'[CA]), ALL('Table'[NOSeller-Name]), VALUES('Table'[NOSeller]))
)

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.