Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Max range axis

Hi everyone, I have 2 column values of data, Cash flow and EBITDA, and initially wished to have both columns in the same axis. However it does not seem possible. Hence, I have arranged them by havin...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    Please try:

    MAX VALUE2 = 
var _a = CALCULATE(SUM('Case'[Actual]),FILTER(ALLEXCEPT('Case','Case'[Date]),[EBITDA]="EBITDA"))
var _b = CALCULATE(SUM('Case'[Forecast]),FILTER(ALLEXCEPT('Case','Case'[Date]),[EBITDA]="EBITDA"))
var _c = CALCULATE(SUM('Case'[Actual]),FILTER(ALLEXCEPT('Case','Case'[Date]),[Cash Flow]="Cash Flow"))
var _d = CALCULATE(SUM('Case'[Forecast]),FILTER(ALLEXCEPT('Case','Case'[Date]),[Cash Flow]="Cash Flow"))
return MAXX({_a,_b,_c,_d},[Value])

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi v-jianboli-msft , greatly appreciate your help!

I tried it in my main dashboard, one thing is my data has quite a number of other fields/filters, and the max range remained large after selecting various filters.

 

One such field example is date which I have added to the 'case' example:

 

Would it be possible to make the max range dynamic, such that the max range follows the various filters: 

 

Thank you.

 

 

 

 

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi Anonymous ,

 

Please try:

MAX VALUE2 = 
var _a = CALCULATE(SUM('Case'[Actual]),FILTER(ALLEXCEPT('Case','Case'[Date]),[EBITDA]="EBITDA"))
var _b = CALCULATE(SUM('Case'[Forecast]),FILTER(ALLEXCEPT('Case','Case'[Date]),[EBITDA]="EBITDA"))
var _c = CALCULATE(SUM('Case'[Actual]),FILTER(ALLEXCEPT('Case','Case'[Date]),[Cash Flow]="Cash Flow"))
var _d = CALCULATE(SUM('Case'[Forecast]),FILTER(ALLEXCEPT('Case','Case'[Date]),[Cash Flow]="Cash Flow"))
return MAXX({_a,_b,_c,_d},[Value])

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi v-jianboli-msft ,

     

    Sorry for the delayed reply.

     

    I have tried it, and I realise I should have mentioned, our data tables look something like this.

    As we have many data tables, we have used a centralised date table as our filter (we also have several other filters):

     

     

    I had added a date table:

     

     

    I used this DAX and set it as the max axis for both charts:

    MAX VALUE =
    var _a = CALCULATE(SUM('Case'[Actual]),FILTER(ALLEXCEPT('Date','Date'[Date]),'Date'[test]>0))
    var _b = CALCULATE(SUM('Case'[Forecast]),FILTER(ALLEXCEPT('Date','Date'[Date]),'Date'[test]>0))

    return MAXX({_a,_b},[Value])
     
    However, the axis does not show the same:

     

    Would it still work for filters that come from related tables?

    Thank you.