Use the current Quarter-To-Date's value (a measure) and make it a slicer.

[mhsieh]

Hi guys, I will try my best to describe what I want to achieve. But let me know what other info I need to provide so that I can be better assisted!

I have this matrix showing all the accounts (here I only show 2 accounts - Academy Sports, camping World), over the months, their Document(850, 855) Exchange Volume. 

855 Complaince is just [855]/[850]
And 855_CP_QTD is the Quarter-To-Date Value of the 855 Compliance. Below is the measure calculation for both.

855 Compliance = 
IFERROR(
    [855] / [850],
    0
)

855_CP_QTD = 
IFERROR(
    [855_QTD] / [850_QTD],
    0
)

I would like to create a Compliance Slicer ranging from 0 ~ 1 (with 0.01 increments). If I select the range of the slicer to be 0 to 0.7, then in the matrix visual, only Camping World has the most recent 855_CP_QTD (0.65) fall into that range, so the matrix will only display Camping World's data.

How do I do that? 

Hopefully my question make sense. But let me know then I can try to explain again!

This is how the table relationships look like

Thank you!!

[mhsieh]

@DataInsights  I'm sorry to bother you again. As I continue to develop this dashboard. I have a couple more questions, hoping you could help me. Now my matrix is reflecting correctly to the filter, but other visuals are not.

Let me know if you think it's better for me to put up another post for this question.

Measures involved:

Number of Retailers - 

Num of Retailers = 
DISTINCTCOUNT(
    Retailer_Compliance[SALESFORCE_ACCOUNT_ID]
)

Line Chart - 

855_CP = 
IFERROR(
    [855] / [850],
    0
    )

Area Chart - 

850 = 
IF(
    ISBLANK(
        SUM(Retailer_Compliance[VOL_850])
    ),
    0,
    SUM(Retailer_Compliance[VOL_850]
    )
)

855 = 
IF(
    ISBLANK(
        SUM(Retailer_Compliance[VOL_855])
    ),
    0,
    SUM(Retailer_Compliance[VOL_855]
    )
)

Thank you!!

[DataInsights]

@mhsieh,

I think the issue is due to the visuals in red not having filter context for Retailer. The matrix has Retailer, so it slices correctly. However, the card (for example) doesn't have Retailer, so it aggregates multiple retailers. Can you share a link to your sanitized pbix, using one of the file services like OneDrive?

I recommend creating a new post (tag me once you create it and I'll take a look).

[DataInsights]

@mhsieh,

You can achieve this with a Numeric Range parameter. See solution below:

https://community.powerbi.com/t5/Desktop/Global-Parameters-Variables-Table/m-p/2695256#M943218 

[mhsieh]

Hi @DataInsights

This is very helpful!

Now I have my filter measure set like this:

Filter = 
IF(
    [855_CP_QTD] < Parameter[Parameter Value],
    1
)

 And it's giving me the below result.

How can I modify my filter measure to get:
If an account's most recent month's [855_CP_QTD] is less than 0.7 (slider), then the account's all data (the whole 1/1 - 7/1) will show.

[DataInsights · Accepted solution]

@mhsieh,

Try this measure:

Filter = 
VAR vMaxAccountDate =
    CALCULATE (
        MAX ( Retailer_Compliance[Start of Month] ),
        ALL ( Retailer_Compliance ),
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_NAME] )
    )
VAR vAmount =
    CALCULATE (
        [855_CP_QTD],
        Retailer_Compliance[Start of Month] = vMaxAccountDate
    )
VAR vResult =
    IF ( vAmount < Parameter[Parameter Value], 1 )
RETURN
    vResult

[mhsieh]

@DataInsights 

I changed the 'Retailer Compiance'[Start of Month] to 'Calendar Lookup'[Start of Month] in your provided code, and it works!! Thank you so much!

Filter = 
VAR vMaxAccountDate =
    CALCULATE (
        MAX ( 'Calendar Lookup'[Start of Month]),
        ALL ( 'Calendar Lookup' ),
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_NAME] )
    )
VAR vAmount =
    CALCULATE (
        [855_CP_QTD],
        'Calendar Lookup'[Start of Month] = vMaxAccountDate
    )
VAR vResult =
    IF ( vAmount < Parameter[Parameter Value], 1 )
RETURN
    vResult

One more question.
Now it's showing accounts that have their most recent [855_CP_QTD] < 0.7.

What if I want to change the slicer to become BETWEEN. Say between 0.5-0.7 and only show accounts whose most recent [855_CP_QTD] is between 0.5 to 0.7.

How would I do that?
 

[DataInsights]

@mhsieh,

Glad to hear that works. For BETWEEN logic, you can create two Numeric Range parameters (From and To). Adjust the IF statement as follows:

Filter =
VAR vMaxAccountDate =
    CALCULATE (
        MAX ( 'Calendar Lookup'[Start of Month] ),
        ALL ( 'Calendar Lookup' ),
        VALUES ( Retailer_Connections_Lookup[ACCOUNT_NAME] )
    )
VAR vAmount =
    CALCULATE ( [855_CP_QTD], 'Calendar Lookup'[Start of Month] = vMaxAccountDate )
VAR vResult =
    IF (
        vAmount >= ParameterFrom[Parameter Value]
            && vAmount <= ParameterTo[Parameter Value],
        1
    )
RETURN
    vResult

[mhsieh]

@DataInsights 
You are saying that I will need to have 2 slicers?

[DataInsights]

@mhsieh,

Yes, one for ParameterFrom and one for ParameterTo.

[mhsieh]

@DataInsights Got everything to work! Thank you again!