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 to display the value with these two filters.

 

DiaEneroFebreroMarzoAbrilMayoJunioJulioAgostoSeptiembre

12222333336444495555126666157777188888219999251110
22222333336444495555126666157777188888219999251110
32222333336444495555126666157777188888219999251110
42222333336444495555126666157777188888219999251110
52222333336444495555126666157777188888219999251110
62222333336444495555126666157777188888219999251110
72222333336444495555126666157777188888219999251110
82222333336444495555126666157777188888219999251110
92222333336444495555126666157777188888219999251110
102222333336444495555126666157777188888219999251110
112222333336444495555126666157777188888219999251110
122222333336444495555126666157777188888219999251110
132222333336444495555126666157777188888219999251110
142222333336444495555126666157777188888219999251110
152222333336444495555126666157777188888219999251110
162222333336444495555126666157777188888219999251110
172222333336444495555126666157777188888219999251110
182222333336444495555126666157777188888219999251110
192222333336444495555126666157777188888219999251110
202222333336444495555126666157777188888219999251110
212222333336444495555126666157777188888219999251110
222222333336444495555126666157777188888219999251110
232222333336444495555126666157777188888219999251110
242222333336444495555126666157777188888219999251110
252222333336444495555126666157777188888219999251110
262222333336444495555126666157777188888219999251110
272222333336444495555126666157777188888219999251110
282222333336444495555126666157777188888219999251110
292222333336444495555126666157777188888219999251110
302222333336444495555126666157777188888219999251110
31  64444 126666157777 219999 

 

and so on with all the months and days of the month.

In what I have researched I managed to filter by day (today's 20th), but I still can't do it by month.

I remain attentive to any comments that allow me to do so.

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.

     

5 Replies

    • 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.