Measures forgetting SELECTEDVALUE?

[BillyT_350]

Hello community. I have a problem where some measures based on other measures appear to be forgetting omnipresent SELECTEDVALUEs.

Most of my reports allow basic unit of measure conversion by selecting one of a handful of hard-coded units of measure (they live in their own table and never change). A user selects the desired unit of measure (UOM) from a single-select dropdown slicer. Because they were sometimes not selecting a UOM in Analyze in Excel, causing measures to break, I added an error message to alert them of their error:

Selected Quantity Type = SELECTEDVALUE('Quantity Type Selection'[Quantity Type], "Select a quantity!")

It is, I think, impossible for a user to get this error when using one of the published PBI reports; every page has a single-select UOM slicer, even if hidden.

This problem is more complex than simple use of measures. The base sets of measures calculate sums and counts of data based on the selected UOM.

Inventory Quantity Perpetual = VAR Q = [Selected Quantity Type]
RETURN SWITCH(TRUE()
    , Q = "Eaches"
        , SUM('Fact Perpetual Inventory'[Inventory Quantity Eaches])
    , Q = "Packs"
        , SUM('Fact Perpetual Inventory'[Inventory Quantity Packs])
    , Q = "LBS"
        , SUM('Fact Perpetual Inventory'[Inventory Quantity LBS])
    , Q -- Returns the error message
    )

Then a second layer of measures calculates these sums or counts over selected time periods (visual specific). The below measures are applied to a matrix with a legend of fiscal week.

Start Inv Qty Sugg **bleep** FWkly = VAR C = [Current_FWOrd] -- Current fiscal week order number on cal table
VAR W = [First_FWOrd] -- Earliest fiscal week order number on cal table
VAR I = --CALCULATE([Current Inv Qty Perpetual], FILTER(ALL('Dim Date'), 1=1))-- Before I updated that calc to have the ALL(Dim Date)
    [Current Inv Qty Perpetual]
RETURN SWITCH(TRUE()
    , W = C
        , I
    , I + CALCULATE([PO Firm Remaining Quantity] + [WO Firm Remaining Quantity] - [Forecast Quantity PIC] + [PO Suggested Remaining Quantity] + [WO Suggested Remaining Quantity], FILTER(ALL('Dim Date'), C <= 'Dim Date'[Fiscal Week Order] && 'Dim Date'[Fiscal Week Order] < W))
)

or

End Inv Qty Sugg **bleep** FWkly = [Start Inv Qty Sugg **bleep** FWkly] - [Forecast Quantity PIC] + [PO Firm Remaining Quantity] + [PO Suggested Remaining Quantity] + [WO Firm Remaining Quantity] + [WO Suggested Remaining Quantity]

Then a third if some of those values fall within certain ranges.

Weeks of Stock Status Sugg **bleep** = 
VAR I = [End Inv Qty Sugg **bleep** FWkly]
VAR F = [Avg Fcst 13 Weeks]
VAR WOS = [Weeks of Stock Sugg **bleep**]
VAR T = [PTWS_Selected] --[PTWS_Last]
VAR L = [Weeks of Stock Excess Limit]
RETURN SWITCH(TRUE()
    , ISBLANK(T)
        , "Agg or no Tgt"
    , I <= 0
        , "OOS"
    , (0 < I && (ISBLANK(F) || F = 0)) || L <= WOS
        , "Excess"
    , 0 < WOS && WOS < T
        , "Blw Tgt"
    , T <= WOS && WOS < L
        , "On Tgt"
    , "ERR"
)

or

Product DCt OOS Sugg **bleep** = -- KEEP IN SYNC WITH TOTAL-LOGIC [WOS Category Sugg **bleep**]
CALCULATE([Product Distinct Count]
    , FILTER('Ref Product'
        , --[Weeks of Stock Sugg **bleep**] <> BLANK() && ([Weeks of Stock Sugg **bleep**] <= 0 || ([End Inv Qty Sugg **bleep** FWkly] <= 0 && ISBLANK([Avg Fcst 13 Weeks])))
            [End Inv Qty Sugg **bleep** FWkly] <= 0 || ISBLANK([End Inv Qty Sugg FWkly])
    )
)

We then have some matrices which users requested which displays those counts. Note the third field, "On Target".

However, if the user attempts to filter by the returns of one of those counts, one of the component measures will throw the error from the first code snippet, "Select a quantity!"

I have to think that these errors must not be correct, but everything I do to dive into them yields the same error from one component calculation or another.

Any ideas on what's going on here?

Maybe, because the measure only returns a count, I need a measure which creates a summarized table, and then maybe I need a measure which does the counting? Then the users would filter on the values in the summarized table, and not the measure?

[v-jingzhang]

Hi @BillyT_350 

Well this is somewhat complicated. Can you first test if all measures work well when the first measure does not have "Select a quantity!" as the alternative value? 

Since the measure is used in other measures, usually all possible results returned by it are expected to have the same data type to avoid data type not matching error. 

Otherwise you may try IFERROR function instead. 

Best Regards,
Community Support Team _ Jing

[BillyT_350]

@v-jingzhang, I may try hard-coding a filter into some of the calculations to see if they will work. The thing is that I have that error message in the SELECTEDVALUE() for a reason; users crunch the dataset in Analyze in Excel as well, and they need that error message to remind them. If a UOM is hard-coded into some measures, then some users may end up comparing apples to oranges.
I've also filed ticket 2211030040007196 to see if some MSFT folks and I can troubleshoot this together; this seems buggy to me.