Forum Discussion

felipe_pinto's avatar
felipe_pinto
Helper I
4 years ago
Solved

Measure - How to define the maxx value using DAX

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,
 
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

11 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    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's avatar
      felipe_pinto
      Helper I
      4 years ago

      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's avatar
    bcdobbs
    Community Champion
    4 years ago

    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's avatar
      felipe_pinto
      Helper I
      4 years ago

      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's avatar
        bcdobbs
        Community Champion
        4 years ago

        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?