Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
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
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] )
)
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?
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])) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
5 | |
4 | |
3 | |
3 |
User | Count |
---|---|
14 | |
9 | |
7 | |
3 | |
3 |