How do I filter out data that shows in one chart that I want to ignore in another?

[Anonymous]

Hello all!

I am knew to power bi and even newer to the community, really hoping I can explain this well enough and hoping you all can help me. 

So in my dashboard I have a slicer and 2 charts. 

1. In my first chart I have Revenue - Labour - Profit <---- these are for each branch 

2. In the other I have budget Variance by branch 

* For simplicity I will refer to them as 1 or 2 

In my slicer I have it set as dates and when I've selected between the month ie July 1 - July 31 I should see the data in 2 

I want to "grey out" or "filter" the data in 2 when there's nothing to show if I were in between two months i.e July 15 - Aug 15 

The slicer for 2 should only work when I'm inside one month at a time. 

Any thoughts? 

[Anonymous]

Hi,

If i understand this correctly, you want to show the chart 2 only when then selected dates are in the same month.

If that is the scenario, try to follow below steps:

MinDate = CALCULATE( MIN( 'Calendar'[date] ), ALLSELECTED( 'Calendar'[date] ) )

MaxDate = CALCULATE( MAX( 'Calendar'[date] ), ALLSELECTED( 'Calendar'[date] ) )

Show Flag = 
VAR v_MinMonthYear = FORMAT( [MinDate], "MM-YYYY" )
VAR v_MaxMonthYear = FORMAT( [MaxDate], "MM-YYYY" )
RETURN
IF( v_MinMonthYear = v_MaxMonthYear, "1", "0" )

Here, the show flag return "1" only when the selected dates are in the same month of the same year.

Then, you can add a visual level filter on the second chart as:

Thanks.

[Anonymous]

Hi Vaibhavdesai, 

I'm having issues with that expression/logic. 

I appreciate your help! 

Please see the changes I have made 

Thank you, 

MinDate = CALCULATE( MIN( 'budgetrevenue' [paydate] ), ALLSELECTED( 'budgetrevenue' [paydate] ) )

MinDate = CALCULATE( MIN( 'budgetrevenue' [paydate]), ALLSELECTED( 'budgetrevenue' [paydate] ) )

//show flag didn't work for me? 
//Am I supposed to change MinDate and Maxdate? 

show Flag = 
VAR v_MinMonthYear = FORMAT( [MinDate], "MM-YYYY" )
VAR v_MaxMonthYear = FORMAT( [MaxDate], "MM-YYYY" )
RETURN
IF( v_MinMonthYear = v_MaxMonthYear, "1", "0" )

[Anonymous]

Hi,

It would help if you could share your pbix.

Do you have seperate calendar or date table in your model?

Thanks.

[Anonymous]

Hello, 

Unfortunately I won't be able to send the pbix, but I have attached an image, does this help? 

I do have a separate Calendar and Date table. 

, 

[Anonymous]

Hi,

I think you are trying to write the code for all the measures in just one measure.

You need to create 3 different measures.

Also note I have used Calendar[Date] for MinDate and MaxDate.

MinDate = CALCULATE( MIN( 'Calendar'[date] ), ALLSELECTED( 'Calendar'[date] ) )

MaxDate = CALCULATE( MAX( 'Calendar'[date] ), ALLSELECTED( 'Calendar'[date] ) )

Show Flag = 
VAR v_MinMonthYear = FORMAT( [MinDate], "MM-YYYY" )
VAR v_MaxMonthYear = FORMAT( [MaxDate], "MM-YYYY" )
RETURN
IF( v_MinMonthYear = v_MaxMonthYear, "1", "0" )

Thanks.