Forum Discussion

jcamilo1985's avatar
jcamilo1985
Helper III
4 years ago
Solved

max in pre-aggregate table

good evening experts again I have a difficulty that I hope you can help me solve, I need to make a measurement, basically it is to assign a custom format, the idea is to obtain the maximum of [amoun...
Need Help
  • jcamilo1985's avatar
    jcamilo1985
    4 years ago

    Goodnight

    It's been a while since I've been back here, the truth is that with so much work sometimes time becomes too limited, however I didn't want to let this post go by without including the solution so that if someone else is in the same predicament they can find it here a guide and a solution.
    Basically the syntax that works is:

     

    =
    CALCULATETABLE (
    ADDCOLUMNS ( ALL ( Calendario[Nombre del día] ), "@hurtos", [cantidad] ),
    REMOVEFILTERS ( Calendario[Número del día] )
    )

    this excellent video explains what is happening, it really is something that is not at all intuitive 

    Finally, the measure was needed to place a conditional format, therefore the final one is as follows:

    color bars =
    VAR table =
    CALCULATETABLE (
    ADDCOLUMNS ( ALL ( Calendar[Day name] ), "@thefts", [amount] ),
    REMOVEFILTERS ( Calendar[Day of week] )
    )
    maximum VAR =
    MAXX ( table, [@thefts] )
    VARE color =
    SWITCH ( TRUE (), [amount] >= maximum, "#8C0000", "#BDBEBF" )
    RETURN
    color
    hope this helps in some way

     

ryan_mayu's avatar
ryan_mayu
Super User
4 years ago

jcamilo1985 

could you pls provide the sample data and expected output?

  • jcamilo1985's avatar
    jcamilo1985
    Helper III
    4 years ago

    Good afternoon

    First of all thank you for coming to my aid.

     

    the result would basically be the highest number of events per day for each day of the week

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      4 years ago

      jcamilo1985 

      maybe you can try this

      Measure 2 = maxx(ALL('Table'[ID]),[Measure])

      • jcamilo1985's avatar
        jcamilo1985
        Helper III
        4 years ago

        first of all thank you very much for coming to my aid, I had previously tried this measure, but I don't know why it is failing in grain of the day, note that it works at the ID level

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi  jcamilo1985 ,

      I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can update the formula of measure [color dias] as below:

      color dias = 
VAR _tab =
    ADDCOLUMNS (
        ALL ( 'Calendar' ),
        "@maxqty",
            SUMX (
                FILTER (
                    ALLSELECTED ( 'dataset de prueba' ),
                    WEEKNUM ( 'dataset de prueba'[fecha_hecho], 2 )
                        = SELECTEDVALUE ( 'Calendar'[Week] )
                        && FORMAT ( 'dataset de prueba'[fecha_hecho], "dddd" )
                            = EARLIER ( 'Calendar'[Nombre del dia] )
                ),
                [cantidad]
            )
    )
RETURN
    MAXX ( _tab, [@maxqty] )

      If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

      How to upload PBI in Community

      Best Regards