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

Calculated column of distinct months given max week of month

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:

  • Filter the table by the year 2018
  • Of each month, look at the max WeekOfMonth
  • Select only the months with WeekOfMonth = 3

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Calculated column of distinct months given max week of month

Hi @IHeardPeopleSay

 

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 

View solution in original post

Super User III
Super User III

Re: Calculated column of distinct months given max week of month

@IHeardPeopleSay

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 

View solution in original post

8 REPLIES 8
Super User III
Super User III

Re: Calculated column of distinct months given max week of month

Hi @IHeardPeopleSay

 

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 

View solution in original post

IHeardPeopleSay Frequent Visitor
Frequent Visitor

Re: Calculated column of distinct months given max week of month

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

Super User III
Super User III

Re: Calculated column of distinct months given max week of month

@IHeardPeopleSay

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 

View solution in original post

Super User III
Super User III

Re: Calculated column of distinct months given max week of month

@IHeardPeopleSay

 

You could also choose to set the restriction on Year = 2018 through a slicer instead of having it hard-coded in the column

IHeardPeopleSay Frequent Visitor
Frequent Visitor

Re: Calculated column of distinct months given max week of month

@AlB

 

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.

IHeardPeopleSay Frequent Visitor
Frequent Visitor

Re: Calculated column of distinct months given max week of month

@AlB

 

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.

Super User III
Super User III

Re: Calculated column of distinct months given max week of month

@IHeardPeopleSay

 

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 

IHeardPeopleSay Frequent Visitor
Frequent Visitor

Re: Calculated column of distinct months given max week of month

@AlB

 

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!

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors