Conditional Filtering

[Anonymous]

Hey Everyone,

I'm pretty new to Power BI Desktop and I'd aprreciate your help.

I created a simple bar chart with months and sales. I also created a slicer based on months. I'm trying to make this scenario happen:

If I choose "October" in the slicer I want the bar chart to shoe me only "July","August","September" and "October".

If I choose "June" in the slicer I want the bar chart to shoe me only "March","April","May" and "June". 

Otherwise, the bar chart will show the value I chose in the slicer.

Any ideas how should I make it happen?

Thanks!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

First you need to create a separate table for slicer filtering.

Then try the following formula to create measure:

Measure = 
var MonthNo = SELECTEDVALUE('Slicer Table'[month_no])
var _6_10 = 
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            'Table',
            'Table'[Month_No] in { MonthNo, MonthNo-1, MonthNo-2, MonthNo-3 }
        )
    )
var other = 
    CALCULATE(
        SUM('Table'[Sales]),
        'Table'[Month_No] = MonthNo
    )
return 
    SWITCH(
        MonthNo, 
        10, _6_10, 
        6,_6_10,
        BLANK(), SUM('Table'[Sales]),
        other
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Genious!

Thanks!

[Ashish_Mathur]

Hi,

Refer to my post here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[AllisonKennedy]

So if you choose "May" you only want to see May? 

You'll need to set the slicer on an unrelated table and then can use DAX to do the filtering. A SWITCH statement would work, otherwise you could use a relationship and a mapping table, but that might get more complex.

[Anonymous]

Yes, if I choose May I would like to see only May.

Can you please demonstrate for me how it actually works ?

Much thanks!