Visuals not reacting to the Measure Filter that I defined using Numeric Range What-If Parameter

[mhsieh]

Hi everyone,

The Current Quarter CP % Filter is a slider filter that I created using the Modeling/What-If Paramater.

I want all the visuals to only show data for retailers having their most current Quarter-To-Date (QTD) 855_CP less than the value defined by the Current Quarter CP % Filter.

The matrix in the screenshot is correctly reflecting the Current Quarter CP % Filter; however, other visuals are not. Please see below screenshot for details.

Here is the a pbix file using the sample data (Drive). I would greatly appreciate your help! 

[mhsieh]

Hello @DataInsights here is the new post followed by this thread: https://community.powerbi.com/t5/Desktop/Use-the-current-Quarter-To-Date-s-value-a-measure-and-make-...

Besides the question above, is there a way I can make the constant line reflect the value defined in Current Quarter CP % Filter?

[DataInsights · Accepted solution]

@mhsieh,

Try these measures. The filter logic is built into these measures, so no visual filter is required for the cards when using these measures.

Num of Retailers with Parameter = 
VAR vTableAccountMaxDate =
    ADDCOLUMNS (
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_ID] ),
        "@MaxDate", CALCULATE ( MAX ( Retailer_Compliance[Start of Month] ) )
    )
VAR vTableAmount =
    ADDCOLUMNS ( vTableAccountMaxDate, "@Amount", [855_CP_QTD] )
VAR vTableFilter =
    FILTER ( vTableAmount, [@Amount] < Parameter[Parameter Value] )
VAR vResult =
    CALCULATE (
        DISTINCTCOUNT ( Retailer_Compliance[SALESFORCE_ACCOUNT_ID] ),
        vTableFilter
    )
RETURN
    vResult

855_CP_QTD with Parameter = 
VAR vTableAccountMaxDate =
    ADDCOLUMNS (
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_ID] ),
        "@MaxDate", CALCULATE ( MAX ( Retailer_Compliance[Start of Month] ) )
    )
VAR vTableAmount =
    ADDCOLUMNS ( vTableAccountMaxDate, "@Amount", [855_CP_QTD] )
VAR vTableFilter =
    FILTER ( vTableAmount, [@Amount] < Parameter[Parameter Value] )
VAR vResult =
    CALCULATE ( [855_CP_QTD], vTableFilter )
RETURN
    vResult

I'm not sure regarding the question about making the constant line reflect the value defined in Current Quarter CP % Filter.

[mhsieh]

@DataInsights You are amazing! I learned a lot more about Power BI through your DAX code.

The only issue left is the line chart and the area chart. They are still not reflected the Current Quarter CP % Filter. I want them to only show data from retailers whose most current Quarter-To-Date 855 CP are less than the Current Quarter CP % Filter.

Learning from your DAX code above for the cards, I was trying to write similar measure to make the line chart work, but I didn't succeed (see below code for my attempt). Hoping you could give me some more guidance! Thank you!

855_cp_test = 
VAR vTableAccountMaxDate =
    ADDCOLUMNS (
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_ID] ),
        "@MaxDate", CALCULATE ( MAX ( Retailer_Compliance[Start of Month] ) )
    )
VAR vTableAmount =
    ADDCOLUMNS ( vTableAccountMaxDate, "@Amount", [855_CP_QTD] )
VAR vTableFilter =
    FILTER ( vTableAmount, [@Amount] < Parameter[Parameter Value] )
VAR vAccountList = 
    SELECTCOLUMNS(
        vTableFilter,
        "ACCOUNT_ID", Retailer_Connections_Lookup[ACCOUNT_ID]
    )
VAR vAccountListDate =
    CROSSJOIN(
        vAccountList,
        VALUES('Calendar Lookup'[Start of Month])
    )
VAR vResult =
    CALCULATE ( [855_CP], vAccountListDate)
RETURN
    vResult

Here is the a pbix file using the sample data (Drive).

[DataInsights]

@mhsieh,

Glad to hear that! I believe you can use the same pattern, changing only the measure being calculated in vResult. Notice that everything else is the same--the filter logic is identical, and once you have the filter result in vTableFilter, you calculate [855_CP] in the context of vTableFilter. You can calculate any measure in the context of vTableFilter.

855_CP with Parameter = 
VAR vTableAccountMaxDate =
    ADDCOLUMNS (
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_ID] ),
        "@MaxDate", CALCULATE ( MAX ( Retailer_Compliance[Start of Month] ) )
    )
VAR vTableAmount =
    ADDCOLUMNS ( vTableAccountMaxDate, "@Amount", [855_CP_QTD] )
VAR vTableFilter =
    FILTER ( vTableAmount, [@Amount] < Parameter[Parameter Value] )
VAR vResult =
    CALCULATE ( [855_CP], vTableFilter )
RETURN
    vResult