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
AFoulk
Helper II
Helper II

SWITCH not working, but individual Measures do

I have used this concept on another BI Report and it works just fine, but I cannot figure out why it will not work on the Report I am working on right now.

If I put the individual Measures into my Matrix Table, they work fine. As soon as I try to us this SWITCH function, my Matrix just spins the dotted wheel for 10+ minutes and ends up erroring out.

I did confirm the Text reference is correctly matching to my Metric table I am using in my Slicer which is turned on for Forced Selection.

Has anyone else had the spinning dot wheel forever also? Any ideas how to fix?

Invoiced by Metric = SWITCH( TRUE(),
                        VALUES( '-Metric Unit/$'[Metric]) = "BdFt", [BdFt Invoiced],
                        VALUES( '-Metric Unit/$'[Metric]) = "Sales", [Gross Sales],
                        VALUES( '-Metric Unit/$'[Metric]) = "UoM", [Invoiced Qty],
                        0)

15 REPLIES 15
giri41
Helper I
Helper I

 

 

any one help me please, i cannot get switch funtion to work?

 

those values actual sales, budget sales i cannot return the values, because my switch function doesnt give me an option ???

Hello @giri41 , I have my switch function working fine for different units of measure of our material. I decided for now to keep my Sales Dollars as seperate functions because of formating. I will likely do a switch for different Dollar when I incorporate Net Sales.

 

Can you share what you are trying to do for your SWITCH?

Thanks for your reply.

 

My problem is not able get table columns appear when i use switch function

 

switch (tru(), a=b, c,d)           <----i cannot get to see table value c) 

 

any help will be much appreciated.

 

do i need to press any keys to see the table columns or what is that i am not doing. when i watch videos when the press the first letter power bi pops up with options table columns for me it s not working?

 

is it becasue the values coming SAP HANA.

 

regards

Giri

@giri41 Switch is relying on that True statement to determine what result to show in the column (if table/matrix) or chart value. Whatever your trying to prove is True or False needs to be within the filtering context or part of a disconnected slicer (depending how you are doing this).

 

You may want a manuallly created table

SalesMetric with column named SalesType and the values could be Sales and Revenue.

*remember to set your slicer to be Single Select (my preference is go to the Marketplace and use the Chiclet Slicer) but you can also use the Power BI slicer and turn it into a horizontal so it has the buttons. The Power BI Slicer can be syned across report pages if you need to for any reason.

 

Then with Measures already created for Sales and Revenue (make use they already work properly in a visual on their own).

 

Your switch measure would then be:

 

Sales_Revenue =

VAR __SalesType = VALUES( 'SalesMetric'[SalesType] )

RETURN

SWITCH( __SalesType,

              "Sales",[SalesMeasure],

              "Revenue",[RevenueMeasure],

0)

dedelman_clng
Community Champion
Community Champion

Try using a variable

 

Invoiced by Metric = 
var __Metric = VALUES( '-Metric Unit/$'[Metric])
return
 SWITCH( __Metric,
              "BdFt", [BdFt Invoiced],
              "Sales", [Gross Sales],
              "UoM", [Invoiced Qty],
          0)

More details on variables and SWITCH statements can be seen here:

 

Variables in IF and SWITCH

 

Hope this helps

David

Hi David, I am using the below Measure now, but it still sits there spinning its wheel.

 

Invoiced by Metric =
VAR vMetric = VALUES( '-Metric Unit/$'[Metric])
return
SWITCH( vMetric,
            "UoM", [Invoiced Qty],
            "Sales", [Gross Sales],
            "BdFt", [BdFt Invoiced],
0)

 

 

 

Try using SELECTEDVALUE instead of VALUES

 

Without having the whole pbix file, any further troubleshooting is purely speculative.

Thank you for your help and pointing me back to sqlbi.com. This still hasn't resolved the issue for me unfortunately. But I plan to jump back on sqlbi and checkout that Optimizing DAX cource. I have that site on my links bar but haven't spent much time checking it out yet. I do a lot of my learning off of EnterpriseDNA since its a 1 time lifetime membership cost.

 

Maybe the trick is going to be optimizing the underlying measures differently somehow.

I went back into my individual measures and incorporated Variables there in hopes of speeding it up, but it still sits and spins the wheel at me when trying to use the Switch

Assuming you're doing this in PowerBI Desktop, do you have a recent version? Possible that either your install needs to be repaired or your computer resources could be the limiting issue.  If you publish to the service, does it work? How many rows are in your model?

Yes I have the October release and am doing this on Desktop. My assumption is if it won't work on the Desktop it will not change by being published.

If you mean computer resources by CPU, memory, bit version, etc, that shouldn't be the case either. I had IT get me a beefy model earlier this year and converted everything to 64-bit as well.

As for Rows, it might be between 600k-1m rows of Invoiced data feeding all 3 Measures from that same table.

The latest release came out Oct 17 so if you got it before then it may be a buggy October version.

 

Given the row counts, you might want to look to see if you've accidentally created a Cartesian product - "joining" all of the rows to another "copy" of the table itself. Bask in my SQL days, 1M rows * 1M rows would create enough operations to manifest in the way you have described. 

I downloaded and installed Oct 2018 v2.63.3272.40262 on the 16th. I just downloaded against and installed and it seems to be the same version.

Do you know which version it should be for the latest version?

I'm looking back over each Measure and I'm not using a CrossJoin or anything similar in any of them. Each seperate Measure is only using Calculate, Filter, SumX, Related, and Variable functions within them. The part that is confusing me the most is the fast that each Measure works within the Matrix, even with all 3 in there together. It only sits spinning the wheel when I try to use the Switch Measure.

Is there any way to share the pbix (after masking sensitive data)?

I really wish I could but that would probably take me days and I can't devote that much time right now to trouble shooting this issue. Right now it just makes more sense for me to retain a seperate Report Tab on my BI Report for each seperate Measure.

Cleaning it up would be complicated since its a very complex model as we are a complete company from harvesting, manufacturing, distrubution, and sales

I am still working on a thorough Composite Model on the side (since its preview and can't be published yet) and I'm hoping once that comes together more I can see if this same scenerio becomes and issue or not. I really hope its just something to do with this older all Import model I have been making adjustments to for the past year.

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.