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.
Hi,
Having a table similar to:
Year Month WeekOfMonth
2018 1 1
2018 1 2
2018 1 3
2018 2 1
2018 2 2
2018 3 1
2018 3 2
2018 4 1
2018 4 2
2018 4 3
2017 1 1
2017 1 2
2017 2 1
2017 2 2
2017 2 3
2017 3 1
2017 3 2
2017 3 3
2017 4 1
2017 4 2
I want to use a calculated column as a page-level filter to have the following table:
Year Month WeekOfMonth
2018 1 1
2018 1 2
2018 1 3
2018 4 1
2018 4 2
2018 4 3
That is:
This is the formula I have for now:
Months5Weeks (2018) = VAR M5W = CALCULATE( DISTINCT(Table[Month]); Table[Year] = 2018; Table[WeekOfMonth] = 5 ) return IF(Table[Month] IN {M5W}; Table[Month]; BLANK())
But using that, I only have the following result:
Year Month WeekOfMonth
2018 1 3
2018 4 3
Which obviously isn't what I'm looking for...
Thanks in advance for the help
Solved! Go to Solution.
Try this:
1. Place the three fields you show in your first table in the rows of a table visual
2. Create a measure like:
ShowMeasure = VAR _Max = CALCULATE ( MAX ( Table1[WeekOfMonth] ), ALL ( Table1[WeekOfMonth] ) ) RETURN IF ( _Max = 3, 1 )
3. Place [ShowMeasure] in the visual level filters and select to show if it is not blank
If you need a column try this:
ShowColumn = VAR _Max = CALCULATE ( MAX ( Table1[WeekOfMonth] ), ALL ( Table1[WeekOfMonth] ) ) RETURN IF ( _Max = 3 && Table1[Year]= 2018, 1 )
It's pretty much the same since we were using the table visual with all columns already. I've added the 2018 restriction
Try this:
1. Place the three fields you show in your first table in the rows of a table visual
2. Create a measure like:
ShowMeasure = VAR _Max = CALCULATE ( MAX ( Table1[WeekOfMonth] ), ALL ( Table1[WeekOfMonth] ) ) RETURN IF ( _Max = 3, 1 )
3. Place [ShowMeasure] in the visual level filters and select to show if it is not blank
@AlBthanks for the swift reply.
I asked for a calculated column because I had to apply it to 45 (sigh) visuals, but if there is no way around it I'll just grit my teeth and do it.
That aside, the measure works, but it's clearly not filtered by a specific year, though I'll mark your post as a solution anyway.
If you need a column try this:
ShowColumn = VAR _Max = CALCULATE ( MAX ( Table1[WeekOfMonth] ), ALL ( Table1[WeekOfMonth] ) ) RETURN IF ( _Max = 3 && Table1[Year]= 2018, 1 )
It's pretty much the same since we were using the table visual with all columns already. I've added the 2018 restriction
It should work. Place all three fields, Year, Month and WeekOfMonth, in a table visual and then use the created calculated column in the filters to show when it is not blank.
Otherwise upload the pbix and I'll have a look . We'll make it work
Strange, it does work today, and can't find what didn't make it work yesterday, most probably a filter I didn't notice.
Anyway, thanks for being so forthcoming!
The ShowColumn formula didn't work, keeps giving the same result I had before:
Year Month WeekOfMonth
2018 1 3
2018 4 3
I'll just use the measure.
You could also choose to set the restriction on Year = 2018 through a slicer instead of having it hard-coded in the column
I would've used a slicer if the higher entities hadn't said that it's 'ugly.'
Though now that I think about it maybe I can just make it invisible.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |