cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ivancito111
Frequent Visitor

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.

Ivancito111_0-1632146697589.png

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

1 ACCEPTED SOLUTION

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

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

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!