SUMX MAXX MINX dynamic X and Y Axis from multiple variables

[thomazinh]

I am needing to configure a dynamic x- and y-axis based on multiple slicer selections. The y-axis needs to be the max value between the two JobNames selected and the x-axis needs to be the min and max of the two JobNames selected. Its easy enough when only one selection is made, but I'm having difficulty when I add in the second selection variable. 

The sample data is linked here. Downloadable PBI File. 

_Dyanmic Axis Line Value = 
VAR SelectedJobs = ALLSELECTED(data2[JobName])
VAR SelectedDates = ALLSELECTED('data2'[TrxDate])
VAR LineValueMX = MAXX(SelectedDates,data2[Total Hours]) // works but is summing the values together for each date for example, on 03/31/31 MX has 33 people and CN has 32 people, it is returning 65 as the max when it should return 33
RETURN
LineValueMX 

For the selections made, I'd expect the x-axis to start at 5/25/20 and to end at the max date, 4/15/22. The y-axis should register a max around 1500. Both graphs should abide by the same scale. 

Thank you in advance, and let me know if there is anything I can add.

[v-kkf-msft]

Hi @thomazinh ,

Please try the measure:

MAX_Y-AXis = 
MAXX ( SUMMARIZE ( data2, data2[TrxDate], data2[JobName] ), [Total Hours] )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thomazinh]

@v-kkf-msft 

This partically gets me there. I am wanting the MAXX value to be the same value between the two graphs. When I enter the MAX Y measure into the Y-axis conditional formatting range, it is not considering the max value between the two visuals.

[v-kkf-msft · Accepted solution]

Hi @thomazinh ,

Please try the following formula:

Measure_Yaxis = 
MAXX ( SUMMARIZE ( data2, data2[TrxDate], data2[JobName] ), [Total Hours] )

MAX_Y-AXis = 
VAR NoVCD_Max =
    CALCULATE ( [Measure_Yaxis], data2[DisciplineName] <> "VCD" )
VAR VCD_Max =
    CALCULATE ( [Measure_Yaxis], data2[DisciplineName] = "VCD" )
RETURN
    MAX ( NoVCD_Max, VCD_Max )

Max_X-Axis = 
VAR NoVCD_Max =
    CALCULATE ( MAX ( data2[TrxDate] ), data2[DisciplineName] <> "VCD" )
VAR VCD_Max =
    CALCULATE ( MAX ( data2[TrxDate] ), data2[DisciplineName] = "VCD" )
RETURN
    MAX ( NoVCD_Max, VCD_Max )

Min_X-Axis = 
VAR NoVCD_Min =
    CALCULATE ( MIN ( data2[TrxDate] ), data2[DisciplineName] <> "VCD" )
VAR VCD_Min =
    CALCULATE ( MIN ( data2[TrxDate] ), data2[DisciplineName] = "VCD" )
RETURN
    MIN ( NoVCD_Min, VCD_Min )

Then you need create a calendar table, and use the calendar[date] column as the x-axis of these charts.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.