Dynamic EVERYTHING (measures, axis, legend, titles, chart types) 08-11-2020 01:02 AM - last edited 08-11-2020 01:03 AM

[rgarancs]

This solution shows layered dynamic DAX measures, i.e. how one chart can show different values, in different time frames, different scale. Report also shows how a slicer can be used to dynamically change axis and legend. Report has dynamic title and switch for chart types. Database layout is quite simple (and can be used for other cases) but uses a bit of trickery to create such dynamic solution using slicers to change axis/legend. 

This is how some steps are made:

Dynamic_value = SWITCH(SELECTEDVALUE(Value_filter[Value field],"Volume"),
"Volume",SUM('Master'[Volume]),
"Value",SUM('Master'[Value]),
"Price",DIVIDE(SUM('Master'[Value]),SUM('Master'[Volume])),
"Distribution",DIVIDE(SUM('Master'[Distribution relevant rate]),SUM('Master'[Volume])))

Step2:

Dynamic_value_step_2 = SWITCH(SELECTEDVALUE(tbl_time[Time],"Free range"),
"Month-to-date",TOTALMTD([Dynamic_value],Master[Date].[Date]),
"Year-to-date",TOTALYTD([Dynamic_value],Master[Date].[Date]),
"Full year",CALCULATE([Dynamic_value],ALL(Master[Date].[Date])),
"Free range",[Dynamic_value])

 Last step:

Dynamic_value_step_4 = SWITCH(SELECTEDVALUE(tbl_vs[versus],"absolute numbers"),
"absolute numbers",[Dynamic_value_step_3],
"versus LY",[Dynamic_value_step_3]-CALCULATE([Dynamic_value_step_3],SAMEPERIODLASTYEAR(Master[Date].[Date])),
"versus LM",[Dynamic_value_step_3]-CALCULATE([Dynamic_value_step_3],DATEADD(Master[Date].[Date],-1,MONTH)),
"versus LQ",[Dynamic_value_step_3]-CALCULATE([Dynamic_value_step_3],DATEADD(Master[Date].[Date],-1,QUARTER))
)

Dynamic title:

Title 1 = var val = SELECTEDVALUE(Value_filter[Value field])
var ax = SELECTEDVALUE('Database axis'[Attribute])
var leg = SELECTEDVALUE('Database legend'[Attribute])
var yrs = SELECTEDVALUE(Master[Date].[Year])
var mnh = SELECTEDVALUE(Master[Date].[Month])
var qrt = SELECTEDVALUE(Master[Date].[Quarter])
var ver = SELECTEDVALUE(tbl_vs[versus])
var tme = SELECTEDVALUE(tbl_time[Time])
var scl = SELECTEDVALUE(tbl_scale[Scale])
return
TRIM(val& " for "& ax & " split by "& leg&": "&yrs&" "&qrt&" "&mnh&" "&SWITCH(tme,"Month-to-date","MTD","Year-to-date","YTD","Free range",BLANK())&" "&IF(ver="absolute numbers",BLANK(),ver)&" "&IF(scl="Thousands","in "&LOWER(scl),BLANK()))

eyJrIjoiZGQxZjRmNDctNWMyMi00MjUwLTg5MDctNmY1YmQ4YWVlNjk1IiwidCI6ImEwNGM1MWNiLTUyOTYtNDE3ZC1iNjEyLWRjMzNhMDI4ZDdhMCIsImMiOjh9

[Anonymous]

Hi @rgarancs ,

Your visual is indeed very dynamic. I am looking for something similar. I downlaoded your .pbix version but I open it, it ask me to install R else few features aren't working. I do not work on R/Python. Does your workbook all functionalities works only with R? When I am using it without R, I cannot see any logics you have designed. Like when I click on Database Legend table or any column, it is not showing me DAX behind it.

Regards

[perezco]

HI @rgarancs ,

Nice work  and extremelly dynamic!!  😍

 Could be possible that you can provide general details of what need to be considered at the moment to do tables for the model that you made? 

Regards,
Cristina 

[rgarancs]

Hi, @perezco ,

Any normal databse with KPIs in seperate columns should work with this setup as long as all fact data is in the same table (have not yet tested multi-fact table solution myself).

If you need both dynamic axis and dymanic legend with cross filtering function, that will make it more difficult in terms of database setup, but in general you need to have a regular fact table to which you add index column and then unpivot all values, seperately there is a mirror table that removes values but keeps only names (e.g. KPI names) which then are liked with both directions. 

You can see the end result in the .pbix file, it has two sets of same data to include cross filtering and legends, thus this databse can be simplified if less features are required. 

Hope this helps. 

[Greg_Deckler]

Seems like this would require a very specific dataset layout but can't tell because no PBIX.

[rgarancs]

You're right, it does require atypical database layout, but it is quite easy to create and can be replicated. Also, I added the pbix file that can be reviewed and used for similar solutions.