04-15-2020 09:09 AM - last edited 04-16-2020 00:23 AM
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])))
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])
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)) )
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()))
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?
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.
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.