Dynamic Axis question

[Winniethewinner]

Hi PBI experts, 

I know there are a couple of posts already on this Dynamic Axis topic, but I'd like some suggestions based on my scenario.

Background: my Power BI report has several data tables, each of the table is connected to a database via ODBC and we write SQL to get data. Because we use vpn and speed is not nice, so overall the data load is very slow.

The data showed below are samples. In the real world my dataset is more complex and has more slicers to be switched. 

First approach I took: I followed Power BI - Dynamic Axis via Slicer (No DAX) and duplicate and unpivot one of my tables, it exactly achieves what I want: 

                

Issues with this approach: my dataset is big and because of the slow connection, adding index column/duplicating table/ unpivoting table - each step takes a long time. In addition, it takes up a lot resources and makes the file even bigger.

Second approach I took: Query1 is my data table. I created a dimention table (no relationship to other tables) by using:

DimTable = UNION(
CROSSJOIN(ROW("Dim","MARKET_NAME"),VALUES(Query1[MARKET_NAME])),
CROSSJOIN(ROW("Dim", "MONTH"),VALUES(Query1[MONTH])),
CROSSJOIN(ROW("Dim", "DAYOFWEEK"),VALUES(Query1[DAYOFWEEK])))

And created a measure by using:

Sales per selected_Dim = if(HASONEVALUE(DimTable[Dim]),
SWITCH(VALUES(DimTable[Dim]),
"MARKET_NAME",CALCULATE(Sum(Query1[Sales]),TREATAS(VALUES(DimTable[Value]),Query1[MARKET_NAME])),
"MONTH",CALCULATE(SUM(Query1[Sales]),TREATAS(VALUES(DimTable[Value]), Query1[MONTH])),
"DAYOFWEEK",CALCULATE(SUM(Query1[Sales]),TREATAS(VALUES(DimTable[Value]), Query1[DAYOFWEEK]))))

Issues with this approach: although it can achieve the same result in terms of switch Axis, but when I apply slicers of the field, the chart does not filter. For example below the dynamic chart is by MONTH, when I filter MONTH 6 in the slicer, it still shows all months. I would need to give users the flexibility of how they interact with the chart, by filtering the slicers, and by switch dimensions.  

Other approach I know: leverage bookmark and buttons, but I'm very reluctant of using this approach. 

In conclusion, I would like some advise on how am I able to achieve the dynamic Axis, and at the same time allowing the slicer field to filter, for a complex dataset. Any advice would be appreciated. Thanks.  

[Winniethewinner · Accepted solution]

Ok I think I figured out, somehow. Here is what I did (kind of combination of the approach 1 & 2 in my original post):

1. Anyway I would need to create an index column in my source data table Query1. In the Power Query Editor:

It is the relationship key which I used to connect with the dimension table I created later on. 

2. Created a new dimension table by using:

DIMENSION TABLE = 
VAR TABLE1=SELECTCOLUMNS(ADDCOLUMNS(Query1,"ATTRIBUTE","MARKET_NAME","VALUE",Query1[MARKET_NAME]),"ATTRIBUTE","MARKET_NAME","VALUE",Query1[MARKET_NAME],"INDEX",Query1[Index])
VAR TABLE2=SELECTCOLUMNS(ADDCOLUMNS(Query1,"ATTRIBUTE","MONTH","VALUE",Query1[MONTH]),"ATTRIBUTE","MONTH","VALUE",Query1[MONTH],"INDEX",Query1[Index])
VAR TABLE3=SELECTCOLUMNS(ADDCOLUMNS(Query1,"ATTRIBUTE","DAYOFWEEK","VALUE",Query1[DAYOFWEEK]),"ATTRIBUTE","DAYOFWEEK","VALUE",Query1[DAYOFWEEK],"INDEX",Query1[Index])
RETURN UNION(TABLE1,TABLE2,TABLE3)

This DAX combined the needed attributes, values and index # from source data table, without needing to duplicate and unpivot the source data table which takes much longer time for a large or complex dataset. 

3. Created a switch measure:

SALES PER SELECTED_DIM = if(HASONEVALUE('DIMENSION TABLE'[ATTRIBUTE]),
SWITCH(VALUES('DIMENSION TABLE'[ATTRIBUTE]),
"MARKET_NAME",CALCULATE(Sum(Query1[Sales]),TREATAS(VALUES('DIMENSION TABLE'[VALUE]),Query1[MARKET_NAME])),
"MONTH",CALCULATE(SUM(Query1[Sales]),TREATAS(VALUES('DIMENSION TABLE'[VALUE]), Query1[MONTH])),
"DAYOFWEEK",CALCULATE(SUM(Query1[Sales]),TREATAS(VALUES('DIMENSION TABLE'[VALUE]), Query1[DAYOFWEEK]))))

4. Add relationship between dimension table and source data table using Index key:

5. Added chart, Attribute as slicer, then the chart axis value is switched based on the Attribute selected: 

And also - what I wanted to achieve eventually, when I added those three fields as slicers (on the top), and filtered for example MONTH 6, at the same time have Attribute as MONTH, the chart is filtered by 6 only: 

This is exactly what I wanted to achieve.