Forum Discussion

telesforo1969's avatar
telesforo1969
Icon for Helper V rankHelper V
2 years ago
Solved

display multiple results in matrix

 

I need to present different results in a matrix. I have solved an operation, but I don't know how to code to present all the results. I would like to use the Switch function. Is this possible? On the other hand, I want to name the columns with proper names.

 

ResultadosVarios =

VAR SumaA = CALCULATE([SumaValor], FILTER(Valores,Valores[Atributo] = "A" ))
VAR SumaB = CALCULATE([SumaValor], FILTER(Valores,Valores[Atributo] = "B" ))
VAR SumaC = CALCULATE([SumaValor], FILTER(Valores,Valores[Atributo] = "C" ))

VAR SumaAB = SumaA + SumaB
VAR SumaAC = SumaA + SumaC
VAR RestaAC = SumaA - SumaC

VAR Resultado = [SumaValor]
RETURN
IF(
    ISFILTERED(Valores[Atributo]),
  Resultado,
  RestaAC
)
 
Need Help
  • vicky_'s avatar
    vicky_
    2 years ago

    There's one way of doing this - create a new table with the Atributo values + the extra columns you want:

    i.e in a new table, use the DAX:

    ValoresAtributo = UNION(VALUES(Valores[Atributo]), {"SumaAC", "SumaAB", "RestaAC"})

    and use that in your matrix columns. 
    Then you can use the following DAX:

    SWITCH(SELECTEDVALUE(ValoresAtributo[Value]),
   "A", SumaA,
   "B", SumaB,
   ...
   "SumaAB", SumaAB
   ... etc/
)

     

    The other way would be to just create one measure per column use them all in your table.

5 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    There's one way of doing this - create a new table with the Atributo values + the extra columns you want:

    i.e in a new table, use the DAX:

    ValoresAtributo = UNION(VALUES(Valores[Atributo]), {"SumaAC", "SumaAB", "RestaAC"})

    and use that in your matrix columns. 
    Then you can use the following DAX:

    SWITCH(SELECTEDVALUE(ValoresAtributo[Value]),
   "A", SumaA,
   "B", SumaB,
   ...
   "SumaAB", SumaAB
   ... etc/
)

     

    The other way would be to just create one measure per column use them all in your table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi telesforo1969 

    Please try the following Dax:

    sumAB = IF('Table'[Atributo]<>"C",CALCULATE(SUM('Table'[Valor]),FILTER('Table','Table'[Atributo]="A"&&'Table'[FECHA] = EARLIER('Table'[FECHA]))) + CALCULATE(SUM('Table'[Valor]),FILTER('Table','Table'[Atributo]="B"&&'Table'[FECHA] = EARLIER('Table'[FECHA]))),BLANK())

     

    sumAC = IF('Table'[Atributo]<>"B",CALCULATE(SUM('Table'[Valor]),FILTER('Table','Table'[Atributo]="A"&&'Table'[FECHA] = EARLIER('Table'[FECHA]))) + CALCULATE(SUM('Table'[Valor]),FILTER('Table','Table'[Atributo]="C"&&'Table'[FECHA] = EARLIER('Table'[FECHA]))),BLANK())

     

    sumBC = IF('Table'[Atributo]<>"A",CALCULATE(SUM('Table'[Valor]),FILTER('Table','Table'[Atributo]="B"&&'Table'[FECHA] = EARLIER('Table'[FECHA]))) + CALCULATE(SUM('Table'[Valor]),FILTER('Table','Table'[Atributo]="C"&&'Table'[FECHA] = EARLIER('Table'[FECHA]))),BLANK())

     

    Then put the corresponding column into the matrix field:

     

     

    Result:

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • telesforo1969's avatar
      telesforo1969
      Icon for Helper V rankHelper V
      2 years ago
      Thanks, I'm already checking it out, I'll let you know if anything happens.
  • telesforo1969's avatar
    telesforo1969
    Icon for Helper V rankHelper V
    2 years ago

    Comparto la siguiente solución. Agradezco el apoyo que encontré de las difentes soluciones a retos similares.

     

    Measure =
    var _column_name = MAX(ValoresAtributo[Name])
    var _total =FORMAT( COUNTROWS(Valores) , "0")
    var _count = FORMAT(
                        CALCULATE( [SumaValor],  
                            FILTER(Valores,(Valores[FECHA])) , TREATAS({_column_name}, Valores[Atributo])), "0" )

    RETURN
    SWITCH(
        TRUE(),
        _column_name = "SUMAAB", [SUMAAB],
        _column_name = "SUMAAC", [SUMAAC],
        _column_name = "RESTAAC", [RESTAAC],
        _column_name = "tOTAL" , _total, _count
    )