Measure - How to define the maxx value using DAX

[felipe_pinto]

Hello guys,

I have the measure below, wich I need to get the max value. 
This exemple, it shows me the week that had more request in the month. However, I also needed that the measure show me the name of VAR used.

Exemple:

The result of it, is 73 request, that happened in the second week. (perfect). But, I need to set up a card that tell me the name of the weekend too, in this case, it was "Semana 2" (second week).

Concentração DPP =

VAR Semana1 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=1,fGeral[Divisão SAP]=200)

VAR Semana2 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=2,fGeral[Divisão SAP]=200)

VAR Semana3 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=3,fGeral[Divisão SAP]=200)

VAR Semana4 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=4,fGeral[Divisão SAP]=200)

Return

Maxx(

UNION(

Row("MEDIDA",Semana1),

Row("MEDIDA",Semana2),

Row("MEDIDA",Semana3),

Row("MEDIDA",Semana4)

),

[MEDIDA]

)

Could someone help me with this question? 
Regards,

[VahidDM · Accepted solution]

Hi @felipe_pinto 

Try this to find the Name of the week:

Concentração DPP Name = 

VAR Semana1 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=1,fGeral[Divisão SAP]=200)
VAR Semana2 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=2,fGeral[Divisão SAP]=200)
VAR Semana3 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=3,fGeral[Divisão SAP]=200)
VAR Semana4 = CALCULATE(COUNTROWS(fGeral),fGeral[Semana]=4,fGeral[Divisão SAP]=200)
Var _M = 
Maxx(
    UNION(
        Row("MEDIDA",Semana1),
        Row("MEDIDA",Semana2),
        Row("MEDIDA",Semana3),
        Row("MEDIDA",Semana4)
    ),
    [MEDIDA]
)
return
SWITCH(TRUE(),
_M = Semana1,"Semana1",
_M = Semana2,"Semana2",
_M = Semana3,"Semana3",
_M = Semana4,"Semana4"
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[felipe_pinto]

Hello @VahidDM 
Thank you so much 🙏 !!!!! That was what I exactly needed. 🙂 You rock!!!!😎

@bcdobbs and @AlexisOlson  Thank you so much for your time too, you helped me a lot!

All the best to you!!

[bcdobbs]

I think I'd start by redefining your measure like this which should be quicker:

Concentração DPP =

CALCULATE (
	MAXX (
		VALUES ( fGeral[Semana] ),
		CALCULATE ( COUNTROWS(fGeral) )
	),
	fGeral[Semana] <= 4,
	fGeral[Divisão SAP] = 200
)

Then you can get the number of the week by doing something like:

Week Number = 

VAR MaxRows = 
	CALCULATE (
		[Concentração DPP],
		REMOVEFILTERS ()
		)
		
VAR WeekTable =
	ADDCOLUMNS (
		SUMMARIZE (
			fGeral,
			fGeral[Semana]
		),
		"@RowCount", CALCULATE ( 
						COUNTROWS(fGeral),
						fGeral[Semana] <= 4,
						fGeral[Divisão SAP] = 200
					)
	)
	
RETURN 
	FILTER ( WeekTable, [@RowCount] = MaxRows )

Have written it without underlying data model so not tested at all. If it doesn't work, share a PBIX and will be able to correct it.

[felipe_pinto]

Hi @bcdobbs 
Thanks for your reply. I tried using your measure, but the DAX returned a msg "The syntax for MaxRows" is incorrect.

Regards,

[bcdobbs]

That's not where I was expecting an issue! For now can you try replacing that  first variable with 

VAR MaxRows =[Concentração DPP]

Failing that can you see rest of error

or share a demo pbix file?

[AlexisOlson]

It acts as if MaxRows is a reserved word or something similar. Renaming the variable should resolve this.

[felipe_pinto]

Hello @AlexisOlson 

I renamed the variable, but another issue came up. Please, find below.

[AlexisOlson]

Yeah, that one is more expected. A measure cannot return a table.

I don't really know what you're trying to return but you could grab a particular column and value from your filtered table like this:

[...]
RETURN
    MAXX ( FILTER ( WeekTable, [@RowCount] = Semana ), [Semana] )

[felipe_pinto]

@bcdobbs 
I uploaded the file on wetransfer.

Let me know if you need more informations.

Link: 

https://we.tl/t-N7SNcJZmaU

[felipe_pinto]

I Couldnt find the option to upload the file...
I replaced the variable as you asked for, but the issue remains...

[felipe_pinto]

I'm trying to find the option to share the file.