max in pre-aggregate table

[jcamilo1985]

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 [amount] for each day, however as seen in the table for every day it throws the same value, and I don't know what I'm doing wrong.

- the fact table is related to a calendar table by date
- the measure [amount] is simply a countrows of the fact table

any help will be welcome. Thank you

[jcamilo1985 · Accepted solution]

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

[tamerj1]

Hi @jcamilo1985 
I have just simulated your measure using a similar dataset and seems to works just fine. See screencshot below.

What results are you getting? Not sure if you have other slicers or filters that are affecting the result.

[tamerj1]

Hi @jcamilo1985 

What kind of relationship is there beween the two tables? You are slicing by day name from the calendar table, right?  Would you please try retun COUNTROWS ( resumen ) and see what results you get. My understanding that you want the max value fro each day name among all weeks so I believe ADDCOLUMNS should be iterating over VALUES ( Year- Week )

[jcamilo1985]

This is how the relationship between calendar and facts is constructed.

 In effect what I am looking for is that for each day of the week the value is 75653 for this case

[tamerj1]

@jcamilo1985 

What is the code of [Amount]?

[ryan_mayu]

@jcamilo1985 

could you pls provide the sample data and expected output?

[jcamilo1985]

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

[v-yiruan-msft]

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

[ryan_mayu]

@jcamilo1985 

maybe you can try this

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

[jcamilo1985]

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