Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
1 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
2 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
3 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
4 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
5 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
6 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
7 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
8 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
9 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
10 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
11 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
12 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
13 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
14 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
15 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
16 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
17 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
18 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
19 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
20 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
21 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
22 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
23 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
24 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
25 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
26 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
27 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
28 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
29 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
30 | 2222 | 33333 | 64444 | 95555 | 126666 | 157777 | 188888 | 219999 | 251110 |
31 | 64444 | 126666 | 157777 | 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.
Solved! Go to 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.
@Ivancito111 You would likely have better luck if you unpivoted your month columns in Power Query.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |