10-23-2018 06:23 AM
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],
10-23-2018 06:29 AM - edited 10-23-2018 06:38 AM
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:
Hope this helps
10-23-2018 06:59 AM
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])
"UoM", [Invoiced Qty],
"Sales", [Gross Sales],
"BdFt", [BdFt Invoiced],
10-23-2018 08:21 AM
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.
10-23-2018 08:34 AM
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
10-23-2018 09:31 AM
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?
10-23-2018 09:37 AM
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.
10-23-2018 09:41 AM
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.
10-23-2018 11:22 AM
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.