locked
Filter ALLEXCEPT caractere RRS feed

  • 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 -



    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





    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 -




    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

    I'm trying to do different,
    I want slicers to filter everything that is ProductClass = "PR"

    CALCULATE (
        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.com

    Wednesday, 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 -



    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:

    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 -


    That was easy. It worked. I've been trying for hours and I did not realize. Thank you
    Wednesday, June 7, 2017 11:31 AM