cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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 [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

1 ACCEPTED SOLUTION
Helper III

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 (
REMOVEFILTERS ( Calendario[Número del día] )
)

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

10 REPLIES 10
Helper III

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 (
REMOVEFILTERS ( Calendario[Número del día] )
)

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

Super User

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.

Super User

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 )

Helper III

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

Super User

What is the code of [Amount]?

Super User

could you pls provide the sample data and expected output?

Proud to be a Super User!

Helper III

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

Community Support

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 =
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] )
),
)
)
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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

maybe you can try this

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

Proud to be a Super User!

Helper III

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

Announcements