Display only two rows month wise in Table Visual with DAX

Hi,

I am trying to achieve the table with comments as seen in the image below … I wanted to display only two rows for each month but I am getting them as below

DAX Calculations

June = if([period] = MONTH(today())-1, program[comments],blank())
July = if([period] = MONTH(today()), program[comments],blank())
August = if([period] = MONTH(today())+1, program[comments],blank())

Current State PowerBI

Desired State

Thanks,
Jayden

@Archer,

Welcome to the forum – glad to have you here!

This seems like it will be pretty straightforward to work out, but it’s difficult to do so without seeing your PPIX file. If you could please post that, I’m sure you’ll get a quick and specific response on this one.

If your data is confidential, here’s a quick video on how to anonymize it so that it can be posted on the forum.

Thanks.

  • Brian

Hi @Archer, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @BrianJ

Thanks for this video quite informative and new thing I learnt
Please find attached PBIX… I have put it in table & matrix visual
As you can see, the table visual has all desired results output but within matrix I can only display first or one comment per month wherein I might get 3 or 4 or many for some programs …
Thank you,
Jayden

MilestonesSample.pbix (70.8 KB)

@Archer,

This is a great example of why it’s always important to look at the underlying data and data model in the PBIX. What jumped out to me as soon as I looked at it is that you are trying to manage time-based data without a Date Table, just using calculated columns in your fact table. This is always going to cause major problems. Thus, what I did was added the Extended Date Table to your data model. In order to build a relationship between the date table and your Program Footprint Milestones table, in Power Query I created a new field called First Day of the Month, which I created from the month and year fields for each milestone.

Now your data model looks like this, and can easily manage time-intelligence based measures and visuals:

Once that was done, the next step was to build a measure that would return the correct milestone data for each month. A rule of DAX is that a measure must return a scalar (i.e., a single value). However, we can use one of my favorite tricks, the combination of the CONCATENATEX() function and UNICHAR( 10) which returns a hard return as the delimiter in the CONCATENATEX() function, to return a scalar value that actually contains multiple milestones:

ConcatX Milestones = 

CALCULATE(
    CONCATENATEX(
        program_footprint_milestones,
        program_footprint_milestones[Milestones],
        UNICHAR( 10 )
    ),
    FILTER(
        program_footprint_milestones,
        program_footprint_milestones[Milestones] <> BLANK()
    )
)

Put it all together in the matrix and presto!

I hope this is helpful. Full solution file below.

2 Likes

This is Great thanks for pointers ! Earlier, I was able to use quick measure on top of milestone and get the concatenate

however when I go this route I can’t highlight each milestone/comment with an icon towards left of the data as depicted in my original screenshot… Please advise

@Archer,

I’m enjoying this thread immensely, since I think UNICHAR and CONCATENATEX are the two most underrated DAX functions (I’m actually working on a two part video series on the cool stuff you can do with these), and once again they are useful here. So based on Status ID, I gave each milestone an icon using the proper UNICHAR code. Status 3 gets a diamond, Status 2 get a shrug and Status 1 gets a toilet (I suspect you will make different choices for your report…)

Status ID Icon = 

VAR Diamond = UNICHAR( 128142 )
VAR Toilet =  UNICHAR( 128701 )
VAR Shrug = UNICHAR( 129335 )
VAR BigRedX = UNICHAR( 10060 )

VAR StatusIcon =
SWITCH( TRUE(),
    SELECTEDVALUE( program_footprint_milestones[status_id] ) = 1, Toilet,
    SELECTEDVALUE( program_footprint_milestones[status_id] ) = 2, Shrug,
    SELECTEDVALUE( program_footprint_milestones[status_id] ) = 3, Diamond,
    BigRedX
)

RETURN
StatusIcon

Then we can pair the icon measure up with CONCATENATEX and UNICHAR( 10 ) like we did above to return the icon values to the matrix:

ConcatX Icon = 

CALCULATE(
    CONCATENATEX(
        program_footprint_milestones,
        [Status ID Icon],
        UNICHAR( 10 )
    ),
    FILTER(
        program_footprint_milestones,
        program_footprint_milestones[Milestones] <> BLANK()
    )
)

And then put together, we have this:

Revised Solution file below.

  • Brian

P.S. Here’s a crazy comprehensive list of unicode characters:

eDNA Forum - Milestones solution.pbix (148.4 KB)

2 Likes

@Archer,

One thing I forgot to mention in my previous response, is that I am not a fan at all of the use of quick measures. While I understand why Microsoft created them to ostensibly decrease the learning curve for new users, I would strongly recommend bypassing them completely and writing your DAX from scratch. It will definitely take you longer in the beginning, but your understanding will be far, far greater. In addition I’ve also seen cases where the quick measure created does not do exactly what the user intended/expected it to do.

Similarly, I think the auto-create relationship settings are the worst feature of this program given that at least half the time, the relationships they create are comically incorrect and the resulting data model becomes unworkable. You wouldn’t let Power BI babysit your kids or choose your career – don’t let it write your DAX or create your data model… :smiley:

  • Brian
3 Likes