How to ignore a part of slicer?

[Anonymous]

Hi all,

I have seen a lot of topic and try a lot, but I don't know why it didn't work in my example.

My data :
ID     NAME  DATE    REGION

111	AAA	1	AFRICA
111	AAA	2	US
111	AAA	3	US
111	AAA	4	CHINA
111	AAA	5	CHINA

And I select DATE between 3 and 5.

I want to ignore the REGION slicer to get REGION in max DATE.

For example:

I choose DATE = 3 to 5 and REGION = US, I want to get CHINA, because the REGION is CHINA when DATE = 5(max DATE).

I try below measure

Measure = CALCULATE(MIN(Table1[REGION]),ALL(Table1[REGION]))

But it didn't work, I can't use ALL function, because I have other slicers in my real data.

Please lead me what I need to do.

Thanks a lot.

Aiolos Zhao

[Ashish_Mathur]

Hi,

I can solve this problem with the PowerPivot in Excel.  However, when i write the same DAX formula in PowerBI desktop, i get a different answer.  In Excel, i created 2 slicers - one for Date in which i selected 3,4 and 5 and another for Region in which i selected US.  I then wrote these 2 measures

Max date=CALCULATE(MAX(Data[Date]),ALL(Data[Region]))

Measure1=LOOKUPVALUE(Data[Region],Data[Date],[Max date])

The result of the Max date measure (see the Excel screenshot) is 5 and this is the correct answer.  5 is the maximum date irrespctive of the chosen region.  The result of Measure1 is correct i.e. China.  So the Excel solution works very well.

Surprisingly, the result of the Max Date measure in PowerBI desktop is 3 due to which measure1 return the wrong answer of US.

You may download my MS Excel solution from here.

Excel solution works very wellMax date measure in PBI returns the wrong answer of 3

[vivran22]

@Ashish_Mathur 

Thank you for guiding in this context.

I made one minor modification in the PBI solution, and it is working now.

MAx Date = CALCULATE(MAX(TableName[DATE ]),ALL(TableName))

@Anonymous 

Please download the pbix file here

Rgds,

Vivek

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

[Ashish_Mathur]

You are welcome Vivek.  Your formula will always return 5 even if the date range chosen in the slicer is 2,3 and 4.  That is not the desired result.

[Anonymous]

Hi @Ashish_Mathur , @vivran22 

Really thanks for reply,

yes, like Ashish said, I can not use all(tablename) or all(date), because when I select 2 to 4, it will return 5.

I don't know why all(region) is not working.

So I didn't find the solution for now, and I'm still trying....

Thanks.

Aiolos Zhao

[Anonymous]

Anyone knows how to solve this?

[Anonymous]

Anyone knows how to solve this?

[Anonymous · Accepted solution]

Fixed it using a new table which have same date values, then select new table dates and change measure defination.

[Anonymous]

I think this is a basic function for BI tools, any help?

[vivran22]

HI,

It is not very clear from the example shared about the requirement. When you select date (from a date slicer) between 3 and 5, where did you select Region = 3?

Rgds,
Vivek

[Anonymous]

Hi @vivran22 ,

Really thanks for reply, that's my mistake, I have changed it to US, I choose REGION = US.

And I want to get CHINA.

Thanks.

Aiolos Zhao

[Anonymous]

any solution about this?

I really want to make it.

[vivran22]

@Ashish_Mathur 

Will you be able to help?

Thanks,

Vivek

[Anonymous]

Anyone can help with this question?