Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rax99
Helper V
Helper V

How to output multiple selections on a single chart using a switch statement

Hi,

 

So I have some selection buttons created from a switch statement in my report. The statement I have used is the following; 

 

Selection =
SWITCH( TRUE(),
VALUES( 'Metric Selection'[Metric] ) = "Quality", Quality[TotalAvgQualityperAgents],
VALUES( 'Metric Selection'[Metric] ) = "PCS", PCS[TotalAvgPCSperAgent],
VALUES( 'Metric Selection'[Metric] ) = "Sales", Sales[TotalSales],
0)

 

So in a line chart I have this 'Selection' measure in the values section. This selection button works great when either one of the 'Quality', 'PCS' or 'Sales' button is selected individualy, and updates the chart accordingly. However I need to add a sepereate 'All' button that will update the chart and display all values (individual lines) on the single line chart. 

 

Is there any mdifications I can add to the switch statement that will permit this? What I want to avoid is values of each measure to be summed up together into one line one the chart.

 

Any advice would be very much appreciated. Let me know if any further clarification is required.

 

Thanks

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

MeasureQuality =
IF(Selected value('Metric selection'[Metric]="ALL");
Quality[TotalAvgQualityperAgent];
Blank()
)

Then include an "ALL" button aswell? 


Connect on LinkedIn

View solution in original post

9 REPLIES 9
tex628
Community Champion
Community Champion

I dont know if this will work but you could try something like this:

 

MeasureQuality =
IF(Selected value('Metric selection'[Metric]);
Blank();
Quality[TotalAvgQualityperAgent]
)

This should return blank() if metric selection is filtered and otherwise show the Quality value. 
Create one measure for each value and include them in the linechart.

 

Br,

J

 

 


Connect on LinkedIn

Thank you. This logically sounds promising. However I am receiving error when adding both to the values pane;

 

Capture.JPG

 

Ive tried changing the blank() to 0 but this still prompts same error. Any ideas?

tex628
Community Champion
Community Champion

MeasureQuality =
IF(Selected value('Metric selection'[Metric]<>Blank());
Blank();
Quality[TotalAvgQualityperAgent]
)

Try this! 🙂 


Connect on LinkedIn
tex628
Community Champion
Community Champion

Jesus, im not paying attention... like this instead:

 

MeasureQuality =
IF(Selected value('Metric selection'[Metric])<>Blank();
Blank();
Quality[TotalAvgQualityperAgent]
)

Connect on LinkedIn

lol yep I did sus it, hence I deleted my post.

 

So looks like it tries to pass in multple values from 'Selection' when not choosing anything. See below;

Capture2.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I would have thought this would default to 0 if no value/selection was chosen as per my switch statement above. Any ideas?

 

(btw thanks for all your help thus far)

tex628
Community Champion
Community Champion

MeasureQuality =
IF(Selected value('Metric selection'[Metric]="ALL");
Quality[TotalAvgQualityperAgent];
Blank()
)

Then include an "ALL" button aswell? 


Connect on LinkedIn

Brilliant, that did the trick. Thank you very much Smiley Happy

tex628
Community Champion
Community Champion

Good to hear! 🙂


Connect on LinkedIn
v-frfei-msft
Community Support
Community Support

Hi @rax99,

 

Unfortunately, it is really impossible to meet your requirement and there is no proper way to work around. As the measure cannot be treated as category, so we cannot display all values in individual lines, and they will be summed up by default. 

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.