Answered by:
Filter ALLEXCEPT caractere
Question

Hi,
I need help with the filter.
I want to filter all values only where the end is "BG".
I did the formula below but it is not bringing value.
Please someone can help
CALCULATE (
SUM ( DailyBookings[Revenue] ),
ALLEXCEPT ( DailyBookings, DailyBookings[FareBasis] ),
DailyBookings[FareBasis] = RIGHT(DailyBookings[FareBasis],2 = "BG")
)Thanks
Tuesday, June 6, 2017 1:06 PM
Answers

It appears, we do not need to inject addition FILTER () iteration because we are dealing with one column here. So, FILTER() below is redundant:
FILTER ( ALL ( Consulta1[ProductClassCode] ), Consulta1[ProductClassCode] = "PR" ),
According to Microsoft DAX reference filters accepted by CALCULATE() can be of two types:
1) List of values
2) Boolean conditions which is applicable in this case.
Boolean conditions, such as Product[ Color] = “White”. It will work only on a single column because the result has to be a list of values of a single column. CALCULATE() runs a FILTER () in the background implicitly.
( If the Boolean expression references more columns, then you have to write the FILTER iteration in an explicit way)
Thais: How many tables do you have in your model ? Do you confirm it's only one table Consulta1 ? Is this only one flat file you are dealing with, no DimDate ???
Please post a data sample, so this matter can be closed finally.
N 
 Edited by Nick Chobotar Wednesday, June 7, 2017 4:24 AM
 Marked as answer by Thaís Sakamoto Wednesday, June 7, 2017 11:31 AM
Wednesday, June 7, 2017 2:33 AM
All replies

Hello Thais,
You were very close to the solution.
So, little "ABRACADABRA" with RIGHT() function and the code below should return you the correct Revenue for FareBasis ending in BG.
Let me know if it works for you.
= CALCULATE ( SUM ( DailyBookings[Revenue] ), ALLEXCEPT ( DailyBookings, DailyBookings[FareBasis] ), RIGHT ( DailyBookings[FareBasis], 2 ) = "BG" )
Nick 
Tuesday, June 6, 2017 1:56 PM 
Hello Thais,
You were very close to the solution.
So, little "ABRACADABRA" with RIGHT() function and the code below should return you the correct Revenue for FareBasis ending in BG.
Let me know if it works for you.
= CALCULATE ( SUM ( DailyBookings[Revenue] ), ALLEXCEPT ( DailyBookings, DailyBookings[FareBasis] ), RIGHT ( DailyBookings[FareBasis], 2 ) = "BG" )
Nick 
Hi Nick
It works. But I have another problem, and I'm sure that's the sum of the revenue. Well I want to use other filters as an hour, to keep up with the recipe. And here it is showing only a total result.
It's possible?
The correct one is the line graphs that show the sum per hour.
And the filter I try to create is showing the total sum of everything.
I'd like to come up with a result just like the graphics.
I filter in the report by date, month, day and time
CALCULATE (
SUM( DailyBookings[Revenue] ), ALL(DailyBookings[BkHour] ),
ALLEXCEPT ( DailyBookings, DailyBookings[FareBasis] ),
RIGHT ( DailyBookings[FareBasis], 2 ) = "BG",Filter(ALLSELECTED(DailyBookings),'DailyBookings'[BookingDateHour]<=Max('DailyBookings'[BookingDateHour])
Filter(ALLSELECTED(DailyBookings),'DailyBookings'[BookingDate])))
Thanks
 Edited by Thaís Sakamoto Tuesday, June 6, 2017 2:38 PM
Tuesday, June 6, 2017 2:23 PM 
Hi,
Hmm! Little difficulty understanding your logic.
You think you could post your pbix sample here.Thanks, N 
Tuesday, June 6, 2017 2:38 PM 
It's ok. Sorry,
I'll try to explain it clearly. In addition to filtering, "BG",
I want it to obey other filters on the page, like filtering by date, month or time.
When selecting the page filter. I can select by hour or by date, or by month.
I've tried using the selected filtering way.
But I could only put an argument, like filtering by time.
I want to know if I can put more than one selected filt
Filter(ALLSELECTED(DailyBookings),'DailyBookings'[BookingDateHour])
AND
Filter(ALLSELECTED(DailyBookings),'DailyBookings'[BookingDate])
Tuesday, June 6, 2017 2:51 PM 
If you want to further slice the BG revenue, it appears ALLEXCEPT is not needed here. Try this and let me know if it works.
= CALCULATE ( SUM ( DailyBookings[Revenue] ), RIGHT ( DailyBookings[FareBasis], 2 ) = "BG", ALLSELECTED ( DailyBookings[BookingDate] ), ALLSELECTED ( DailyBookings[BookingDateHour] ) )
N 
Tuesday, June 6, 2017 3:16 PM 
If you want to further slice the BG revenue, it appears ALLEXCEPT is not needed here. Try this and let me know if it works.
= CALCULATE ( SUM ( DailyBookings[Revenue] ), RIGHT ( DailyBookings[FareBasis], 2 ) = "BG", ALLSELECTED ( DailyBookings[BookingDate] ), ALLSELECTED ( DailyBookings[BookingDateHour] ) )
N 
Something is strange because the lines on the chart are straight
Tuesday, June 6, 2017 5:46 PM 
That should not happen. That's a relationship issue.
What fields are you putting on the axis, are they all from DailyBookings table?
May I see the screen shot of your data model diagram ?
N 
 Edited by Nick Chobotar Tuesday, June 6, 2017 7:28 PM
Tuesday, June 6, 2017 6:23 PM 
Isso não deveria acontecer. Isso é um problema de relacionamento.
Que campos que você está colocando no eixo, são todos eles da tabela DailyBookings?
Posso ver a captura de tela do seu diagrama de modelo de dados?
N 
Tuesday, June 6, 2017 7:51 PM 
That should not happen. That's a relationship issue.
What fields are you putting on the axis, are they all from DailyBookings table?
May I see the screen shot of your data model diagram ?
N 
Hello
CALCULATE (
I'm trying to do different,
I want slicers to filter everything that is ProductClass = "PR"
SUM ( Consulta1[Revenue] ),
(Consulta1[ProductClassCode] = "PR"),
ALLSELECTED ( Consulta1[BookingDate] ),
ALLSELECTED ( Consulta1[BookingDateHour] ),
ALLSELECTED ( Consulta1[BkHour] ),
ALLSELECTED ( Consulta1[BkMonth] ))Tuesday, June 6, 2017 7:59 PM 
Hi Thaís,
Thanks for your quesiton.
In this scenario, please try below DAX formula:
CALCULATE ( SUM ( Consulta1[Revenue] ), FILTER ( ALL ( Consulta1[ProductClassCode] ), Consulta1[ProductClassCode] = "PR" ), ALLSELECTED ( Consulta1[BookingDate] ), ALLSELECTED ( Consulta1[BookingDateHour] ), ALLSELECTED ( Consulta1[BkHour] ), ALLSELECTED ( Consulta1[BkMonth] ) )
Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.comWednesday, June 7, 2017 1:40 AM 
It appears, we do not need to inject addition FILTER () iteration because we are dealing with one column here. So, FILTER() below is redundant:
FILTER ( ALL ( Consulta1[ProductClassCode] ), Consulta1[ProductClassCode] = "PR" ),
According to Microsoft DAX reference filters accepted by CALCULATE() can be of two types:
1) List of values
2) Boolean conditions which is applicable in this case.
Boolean conditions, such as Product[ Color] = “White”. It will work only on a single column because the result has to be a list of values of a single column. CALCULATE() runs a FILTER () in the background implicitly.
( If the Boolean expression references more columns, then you have to write the FILTER iteration in an explicit way)
Thais: How many tables do you have in your model ? Do you confirm it's only one table Consulta1 ? Is this only one flat file you are dealing with, no DimDate ???
Please post a data sample, so this matter can be closed finally.
N 
 Edited by Nick Chobotar Wednesday, June 7, 2017 4:24 AM
 Marked as answer by Thaís Sakamoto Wednesday, June 7, 2017 11:31 AM
Wednesday, June 7, 2017 2:33 AM 
It appears, we do not need to inject addition FILTER () iteration because we are dealing with one column here. So, FILTER() below is redundant:
That was easy. It worked. I've been trying for hours and I did not realize. Thank you
FILTER ( ALL ( Consulta1[ProductClassCode] ), Consulta1[ProductClassCode] = "PR" ),
According to Microsoft DAX reference filters accepted by CALCULATE() can be of two types:
1) List of values
2) Boolean conditions which is applicable in this case.
Boolean conditions, such as Product[ Color] = “White”. It will work only on a single column because the result has to be a list of values of a single column. CALCULATE() runs a FILTER () in the background implicitly.
( If the Boolean expression references more columns, then you have to write the FILTER iteration in an explicit way)
Thais: How many tables do you have in your model ? Do you confirm it's only one table Consulta1 ? Is this only one flat file you are dealing with, no DimDate ???
Please post a data sample, so this matter can be closed finally.
N 
Wednesday, June 7, 2017 11:31 AM