Updating Measure Based on Parameter Selection in Power B

[nunezjo]

Hello,

I have a parameter that allows me to toggle between two different measures in my report. I created this parameter using the new parameter functionality in the modeling tab of Power BI. Here is the parameter information:

Parameter Table: PARM Gross/Net Script Ct

Fields:

1. PARM Gross/Net Script Ct
2. PARM GROSS/Net Script Ct Fields
3. PARM Gross/Net Script Ct Order
4. Script Ct String

The two rows in the parameter table are:

1. "Cancels Included" | 'LDRSHP_RPT_FAC'[Gross Script Count] | 0 | Gross Script Ct
2. "Cancels Excluded" | 'LDRSHP_RPT_FAC'[Script Count (Excluding Cancels)] | 1 | Script Ct.
   
   

   Paramter table: 

   

Additional Measures: 

Script Count (Excluding Cancels) = CALCULATE(SUM('LDRSHP_RPT_FAC'[GRSS_SCRP_CNT]), ISBLANK('LDRSHP_RPT_FAC'[SCRP_CNL_DT]))

Gross Script Count = SUM(LDRSHP_RPT_FAC[GRSS_SCRP_CNT])

My challenge is that I have a measure, LY Gross Script Ct, which doesn't update based on the selection made using the parameter in a slicer. Here is the DAX for that measure: 

LY Gross Script Ct = 
VAR max_date = CALCULATE(LASTDATE(DT_DIM[DAY_DT]), ALL(DT_DIM))
VAR max_year = CALCULATE(VALUES(DT_DIM[FCL_YR_ID]), ALL(DT_DIM), DT_DIM[DAY_DT] = max_date)
VAR target_year = FORMAT(SELECTEDVALUE(DT_DIM[FCL_YR_ID], max_year) - 1, "General Number")
VAR today_fiscal = CALCULATE(VALUES(DT_DIM[FCL_DAY_OF_YR_ID]), ALL(DT_DIM), DT_DIM[DAY_DT] = max_date)

VAR result = CALCULATE([Gross Script Count], DT_DIM[FCL_YR_ID] = target_year, DT_DIM[FCL_DAY_OF_YR_ID] <= today_fiscal,
DT_DIM[FCL_DAY_OF_YR_ID] in DISTINCT(DT_DIM[FCL_DAY_OF_YR_ID]))

RETURN result

 I want this measure to use either the 'Gross Script Count' or the 'Script Count (Excluding Cancels)' depending on the selection in the slicer, but it seems to be using the 'Gross Script Count' exclusively, regardless of the slicer selection.

Any guidance on how to make the LY Gross Script Ct measure respect the slicer selection would be greatly appreciated. Thank you in advance for your help.

[MFelix]

Hi @nunezjo ,

For this you need to change the logic on the measure using an if statement it would look something similar to this:

LY Gross Script Ct = 
VAR max_date = CALCULATE(LASTDATE(DT_DIM[DAY_DT]), ALL(DT_DIM))
VAR max_year = CALCULATE(VALUES(DT_DIM[FCL_YR_ID]), ALL(DT_DIM), DT_DIM[DAY_DT] = max_date)
VAR target_year = FORMAT(SELECTEDVALUE(DT_DIM[FCL_YR_ID], max_year) - 1, "General Number")
VAR today_fiscal = CALCULATE(VALUES(DT_DIM[FCL_DAY_OF_YR_ID]), ALL(DT_DIM), DT_DIM[DAY_DT] = max_date)
var selectedmeasure = IF(SELECTEDVALUE(PARM Gross/Net Script Ct[PARM Gross/Net Script Ct Order] = 1, [Gross Script Count], [Script Count (Excluding Cancels)]


VAR result = CALCULATE(selectedmeasure , DT_DIM[FCL_YR_ID] = target_year, DT_DIM[FCL_DAY_OF_YR_ID] <= today_fiscal,
DT_DIM[FCL_DAY_OF_YR_ID] in DISTINCT(DT_DIM[FCL_DAY_OF_YR_ID]))

RETURN result

[nunezjo]

I got it to work using the following, although it's not doing what I want it to do. It's not giving me data from Last Fiscal Year. It's giving me data from all years. 

LY Gross Script Ct = 
VAR max_date = CALCULATE(LASTDATE(DT_DIM[DAY_DT]), ALL(DT_DIM))
VAR max_year = CALCULATE(VALUES(DT_DIM[FCL_YR_ID]), ALL(DT_DIM), DT_DIM[DAY_DT] = max_date)
VAR target_year = FORMAT(SELECTEDVALUE(DT_DIM[FCL_YR_ID], max_year) - 1, "General Number")
VAR today_fiscal = CALCULATE(VALUES(DT_DIM[FCL_DAY_OF_YR_ID]), ALL(DT_DIM), DT_DIM[DAY_DT] = max_date)
VAR selectedmeasure = 
    IF(
        SELECTEDVALUE('PARM Script Ct'[PARM Gross/Net Script Ct  Order]) = 1, 
        [Gross Script Count], 
        [Script Count (Excluding Cancels)]
      )


VAR result = CALCULATE(selectedmeasure , DT_DIM[FCL_YR_ID] = target_year, DT_DIM[FCL_DAY_OF_YR_ID] <= today_fiscal,
DT_DIM[FCL_DAY_OF_YR_ID] in DISTINCT(DT_DIM[FCL_DAY_OF_YR_ID]))

RETURN result

[nunezjo]

When trying to use the above I get an error.  It says Paramter is not the correct type. Not sure what I'm doing wrong .