Default drill selection based on other column (live connection: no additional columns)

[aletib]

Hi everyone, searched around but only found workarounds based on the creation of an additional column which is not viable for me as I'm working on a live connection.

Scenario:
column visual displaying values of revenue by week number (1-13) on X axis.

Drill up goes to quarter (name) and year, drill down goes to day and other fields.

The DB contains also a relative, rather than absolute, Quarter Aging field : quarter aging = 0 is always current quarter; 1 next quarter, -1 previous quarter.

I want to save my dashboard with the viauls showing weeks on X-Axis for current quarter but without using quarter aging in the hierarchy: users want to see quarter names when they drill up, not integer numbers (quarter aging's values).

If I leave quarter in the X fields and set Quarter Aging = 0 in the filters, when users drill up, they will see the name of current quarter but not other quarters (because of quarter aging = 0).

If only we had the chance to select default value of quarter through a formula/measure I could do a:

Default Value =
CALCULATE(
    MAX('Fiscal Calendar'[Quarter]),
    FILTER'Fiscal Calendar','Fiscal Calendar'[Quarter Aging]=0)
)

and Default Value would always return the Quarter name to use, but I found no way of doing that 😞

Open to ideas, thx! 😉

[Greg_Deckler]

@aletib Can you convert your Live connection to DirectQuery for Power BI and AS? It's a Preview feature that you can turn on.

[aletib]

@Greg_Deckler  No, I can't. Big corporate environment, no support on SSAS (requires laterst SQL/SSQS version as far as I know) and that won't be coming anytime soon for us 😞