Help combining multiple switch measures (Fully dynamic visual)

Question

I have a series of measures that are controlled by several switch tables and slicers that feed into each other to create a dynamic one visual report that allows the selection of any dimension in cumulative or total count/cost displayed by month, quarter, week, or year, but I need help simplifying/restructuring into a single measure. I tried combining the measures into one and using VAR for the inputs, but I can't wrap my head around how to feed from one measure to the next in a combined fashion.----Basic Measures----&nbsp;Project Count = COUNT('Fact Weekly Pipeline Update'[Project ID])&nbsp;--&nbsp;Intake Cost = Sum('Fact Weekly Pipeline Update'[Cost])-----Switching between Count and Cost-----Count/Cost&nbsp;=SWITCH&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;TRUE&nbsp;(),&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;'Switch&nbsp;Table&nbsp;Basic'[Label]&nbsp;)&nbsp;=&nbsp;"Count",&nbsp;CALCULATE&nbsp;(&nbsp;[Project&nbsp;Count]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;'Switch&nbsp;Table&nbsp;Basic'[Label]&nbsp;)&nbsp;=&nbsp;"Cost",&nbsp;CALCULATE&nbsp;(&nbsp;[Intake&nbsp;Cost]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;[Project&nbsp;Count]&nbsp;))--Switch Table Basic--&nbsp;-----Cumulative Totals of Basic Measures Selected in the previous measure-----Cumulative Count/Cost = CALCULATE (&nbsp; &nbsp; 'Basic Measure Slicer'[Count/Cost],&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( 'Calendar'[Date] ),&nbsp; &nbsp; &nbsp; &nbsp; 'Calendar'[Date] &lt;= MAX ( 'Calendar'[Date] )&nbsp; &nbsp; ))&nbsp;--Calendar--&nbsp;&nbsp;----Switching between Cumulative or Non-Cumulative totals of Basic Measures-----&nbsp;Current/Cumulative Count/Cost Switch = SWITCH (&nbsp; &nbsp; TRUE (),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table Cumulative/Current'[Category] ) = "Non-Cumulative", CALCULATE ( [Count/Cost] ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table Cumulative/Current'[Category] ) = "Cumulative", CALCULATE ( [Cumulative Count/Cost] ),&nbsp; &nbsp; CALCULATE ( [Count/Cost] ))&nbsp;--Switch Table Cumulative/Current--&nbsp;-----Displaying the result of the previous measures by Selected Dimensions-----&nbsp;DIM Slicer Current/Cumulative Switch Count/Cost = SWITCH (&nbsp; &nbsp; TRUE (),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Analytics / Infrastructure",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM Analytics Infrastructure'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Codes",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM Codes'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "FP/MP",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM FP/MP'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Funding Model",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM Funding Model'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Project Status",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM New Status'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Project Sub-Status",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM New Sub Status'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Funded/Unfunded",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM Funding'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Project Characteristics",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'Prep Project Characteristics Ind'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Project Condition",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'Prep Project Conditions Ind'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; SELECTEDVALUE ( 'Switch Table DIM Slicer'[Category] ) = "Project Condition/Characteristic",&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Current/Cumulative Count/Cost Switch],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP ( 'Switch Table DIM Slicer'[Labels], 'DIM Project Condition/Characteristics'[DIM] )&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;--Switch Table DIM Slicer--&nbsp;--DIM Funding Model-- (As example, there are several tables set up like this that are reference in the measure above)&nbsp;-----Changing the time series to Year, Quarter, Month, Week-----&nbsp;Date and DIM Slicer Current/Cumulative Switch Count/Cost = SWITCH (&nbsp; &nbsp; SELECTEDVALUE ( 'Date Slicer'[Index Dexc], 1 ),&nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [DIM Slicer Current/Cumulative Switch Count/Cost],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS ( VALUES ( 'Date Slicer'[Period] ), 'Calendar'[Year] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; 2,&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [DIM Slicer Current/Cumulative Switch Count/Cost],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS ( VALUES ( 'Date Slicer'[Period] ), 'Calendar'[Q &amp; Y] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; 3,&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [DIM Slicer Current/Cumulative Switch Count/Cost],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS ( VALUES ( 'Date Slicer'[Period] ), 'Calendar'[M &amp; Y] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; 4,&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [DIM Slicer Current/Cumulative Switch Count/Cost],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS ( VALUES ( 'Date Slicer'[Period] ), 'Calendar'[End of Week] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; [DIM Slicer Current/Cumulative Switch Count/Cost],&nbsp; &nbsp; &nbsp; &nbsp; TREATAS ( VALUES ( 'Date Slicer'[Period] ), 'Calendar'[Year] )&nbsp; &nbsp; ))--Date Slicer(Contains --&nbsp;&nbsp;----Data Model----&nbsp;&nbsp;

v-zhangti · Answer

Hi,&nbsp;Buttersworth&nbsp;
&nbsp;
Based on the data you provided, can you give one or two examples of what your expected output looks like?
&nbsp;
Best Regards,
Community Support Team _Charlotte
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

buttersworth · Answer

Hi&nbsp;v-zhangti&nbsp;,Thanks for responding!The measures work fine in visuals already. The "Date and DIM Slicer Current/Cumulative Switch Count/Cost" and "Period" from "Date Slicer" allow the user to fully control the visual with the filters below:&nbsp;The main chart below, is fully controlled:&nbsp;I really just need help with combining all of these measures into one to simplify and optimize the report. The only issue I have that I am trying to work out seperately, is displaying dates with no data within the selected range. I have been toying with the basic measures (Project Count and Project Cost) to fix this issue, but I think the issue may be coming from another measure higher up in the chain. Either way, getting the measures into one is the main goal and I think that would help me fix the secondary issue of displaying dates with no data.&nbsp;Thank you so much!&nbsp;&nbsp;

Forum Discussion

Help combining multiple switch measures (Fully dynamic visual)

2 Replies

Related content

Combine multiple measures into one

Combine multiple measures in a table

Power Query Combine Multiple Files From Folder

Tables combine

Combining three CONCATENATEX Measures with results from two Tables