Reply
Highlighted
Regular Visitor
Posts: 37
Registered: ‎02-20-2017

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)

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: SWITCH not working, but individual Measures do

[ Edited ]

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

Regular Visitor
Posts: 37
Registered: ‎02-20-2017

Re: SWITCH not working, but individual Measures do

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)

 

 

 

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: SWITCH not working, but individual Measures do

Try using SELECTEDVALUE instead of VALUES

 

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

Regular Visitor
Posts: 37
Registered: ‎02-20-2017

Re: SWITCH not working, but individual Measures do

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.

Regular Visitor
Posts: 37
Registered: ‎02-20-2017

Re: SWITCH not working, but individual Measures do

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

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: SWITCH not working, but individual Measures do

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?

Regular Visitor
Posts: 37
Registered: ‎02-20-2017

Re: SWITCH not working, but individual Measures do

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.

New Contributor
Posts: 450
Registered: ‎08-25-2016

Re: SWITCH not working, but individual Measures do

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. 

Regular Visitor
Posts: 37
Registered: ‎02-20-2017

Re: SWITCH not working, but individual Measures do

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.