Slice by measure result (positive vs negative)

[robarivas]

I have a DAX measure that tells me the variance between currently selected month and the month prior. For a certain dimension (let's say department) the variance can be either positive or negative. I want to add a slicer that lets me filter my visual (a treemap in this case) for either positive variances only or negative variances only. Knowing that a measure can't be a used as a slicer (nevermind the output of a measure) I am stumped as to how to acheive what I'm trying to do. I'd appreciate any help. Thank you.

[v-qiuyu-msft]

Hi @robarivas,

Is the issue solved? How about the result when you try my DAX in original post? If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[robarivas]

For some reason I couldn't get the slicer visual to accept the Status field. However, I believe I resolved the issue using the information/techniques found in this link: http://www.daxpatterns.com/dynamic-segmentation/

[v-qiuyu-msft]

Hi @robarivas,

Glad to hear you have resolved the issue.  Would you please mark helpful replies as answers, so that we can close the thread?

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @robarivas,

In your scenario, assume the measure you created to return variance named Difference, you can create a column to return "Positive" and "Negative" based on the measure values, then place this column in Slicer visual.

Status = if([Difference]>=0,"Positive","Negative")

If the above method doesn't meet your requirement, please share some sample for our analysis.

Best Regards,
Qiuyun Yu

[Vvelarde]

@robarivas

The first solution that came to my head is:

Create a table with 2 rows: Positive , Negative. Use this in slicer.

Next, create a measure:

Measure =
VAR ValtoTreeMap =
    VALUES ( Table1[Values] )
RETURN
    IF (
        HASONEVALUE ( Table2[Type] ),
        IF (
            VALUES ( Table2[Type] ) = "Positive",
            IF ( ValtoTreeMap >= 0, VALUES ( Table1[Values] ), BLANK () ),
            IF ( ValtoTreeMap < 0, ABS ( ValtoTreeMap ), BLANK () )
        )
    )

[Greg_Deckler]

I can think of a possible solution but it is dependent upon the scenario. Can you post some sample data and relationships?