Forum Discussion

Ivancito111's avatar
Ivancito111
Icon for Resolver I rankResolver I
4 years ago
Solved

Occupy a variable with a table name

Hello, good afternoon, I am writing to you because I have a question about a table filter. What happens is that I have a value that changes by month and day, ie, today we are in September 20, I need...
DAX
Help me
Table
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    Ivancito111 The only thing you are left with is a big long SWITCH statement like:

     

     

    Measure = 
  VAR __dia = DAY(TODAY())
  VAR __month = MONTH(TODAY())
RETURN
  SWITCH(__month),
    1, CALCULATE(SUMX('Tabla1', 'Tabla1'[Enero]), FILTER('Tabla1','Tabla1'[Dia]=__dia)),
    2, CALCULATE(SUMX('Tabla1', 'Tabla1'[Febrero]), FILTER('Tabla1','Tabla1'[Dia]=__dia)),
    3, CALCULATE(SUMX('Tabla1', 'Tabla1'[Marzo]), FILTER('Tabla1','Tabla1'[Dia]=__dia)),
    ...
  )

     

    Also, the unpivoting would be in Power Query Editor, not in the source data.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

Ivancito111 You would likely have better luck if you unpivoted your month columns in Power Query.

Ivancito111's avatar
Ivancito111
Icon for Resolver I rankResolver I
4 years ago

But I still have the problem that it must choose the column depending on the month in which we are.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Ivancito111 If you unpivot the columns then no, you are simply filtering rows at that point. This is why it works better than having rows spread out across the table. If you unpivot then you should have:

    Dia, Attribute, Value

    1, Enero, 111

    1, Februaro, 1111

     

    So, all you have to do is filter Attribute for the month you want. No column selection needed, it is always Attribute.

    • Ivancito111's avatar
      Ivancito111
      Icon for Resolver I rankResolver I
      4 years ago

      I know that your answer solves my problem, but isn't there a method that doesn't ruin my board?

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Ivancito111 The only thing you are left with is a big long SWITCH statement like:

         

         

        Measure = 
  VAR __dia = DAY(TODAY())
  VAR __month = MONTH(TODAY())
RETURN
  SWITCH(__month),
    1, CALCULATE(SUMX('Tabla1', 'Tabla1'[Enero]), FILTER('Tabla1','Tabla1'[Dia]=__dia)),
    2, CALCULATE(SUMX('Tabla1', 'Tabla1'[Febrero]), FILTER('Tabla1','Tabla1'[Dia]=__dia)),
    3, CALCULATE(SUMX('Tabla1', 'Tabla1'[Marzo]), FILTER('Tabla1','Tabla1'[Dia]=__dia)),
    ...
  )

         

        Also, the unpivoting would be in Power Query Editor, not in the source data.