topic Re: Format a single cell in a Table (today) - Is this approach sensible? in DAX Commands and Tips

Format a single cell in a Table (today) - Is this approach sensible?

BI_Curious — Sun, 15 Mar 2020 13:37:03 GMT

Hi All,

First post here, but have been using Power BI for a little while now ...

My question is about the validity of my approach. I'd appreciate any feedback about the approach and better / simpler was to achieve what I have done. For clarity, my solution works and achieves the desired outcome.

The data comes from Excel and is a record of my daily weight.

Objective:

Identify today’s date cell in a table by applying conditional formatting (in my case I chose a tick mark)

Approach:

Create a measure containing the row date
Create a measure that compares the above measure with “Today()”
Apply conditional formatting to the table column “Date”

Details:

Create 1st measure:

Weight_Date = max('Weight_Data'[Date])

Create 2nd measure:

Is Today = IF(DATEDIFF(TODAY(),Weight_Data[Weight_Date],DAY)=0,1,0)

Apply formatting:

And here's the result:

So what's the verdict? Is this the most elegant way to acheive my objective or am I missing a simple alternative?

Look forward to all comments!

Re: Format a single cell in a Table (today) - Is this approach sensible?

Greg_Deckler — Sun, 15 Mar 2020 15:05:09 GMT

Nope, that's about the crux of it. Limited options given that conditional formatting only supports numeric values.

Re: Format a single cell in a Table (today) - Is this approach sensible?

BI_Curious — Sun, 15 Mar 2020 15:15:55 GMT

Greg_Deckler Thanks for confirming that I wasn't being a complete numpty 😀 It's always good to get confirmation that you're going along roughly the right tracks 👍

Re: Format a single cell in a Table (today) - Is this approach sensible?

Anonymous — Sun, 15 Mar 2020 15:27:07 GMT

[Is Today] is NOT a measure. It's a calculated column. It could be written in a more compact way:

[Is Today] = 1 * ( TODAY() = Weight_Data[Weight_Date] )

Best
D

Re: Format a single cell in a Table (today) - Is this approach sensible?

BI_Curious — Sun, 15 Mar 2020 16:39:50 GMT

Anonymous That's EXACTLY what I was looking for! Such elegant code and presumably lighter on the system - Brilliant!

As for the DAX being a calculated column, is that "just" a terminology thing? Can you say why it's not a measure? I created it as a measure and it shows as a measure (and it might even taste like a measure 😆) but I'm keen to ensure that my understanding is as complete as possible, so all info gladly received.

Re: Format a single cell in a Table (today) - Is this approach sensible?

Anonymous — Sun, 15 Mar 2020 16:50:18 GMT

A measure's value is calculated on demand and is fully dynamic; you can't slice by a measure (but you can filter by its values). A calculated column is a column in a model table and the values in it are calculated once - at load-time. You can then place the values in a slicer.

Best
D

Re: Format a single cell in a Table (today) - Is this approach sensible?

Greg_Deckler — Sun, 15 Mar 2020 17:01:36 GMT

I hope we as a community are not going down the path of Perl in writing esoteric, unreadable code solely in the name of making things as short as possible...

Re: Format a single cell in a Table (today) - Is this approach sensible?

Anonymous — Sun, 15 Mar 2020 17:08:19 GMT

I'd suggest, Greg_Deckler, you should check out most of your measures since they tend to be more complex than necessary most of the time.

Last example can be found here:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-to-facilitate-filters-whether-True-for-group-s-selected/m-p/974870/highlight/true#M11751

If you compare it with my solution, you'll know immediately why you should not make such comments.

Best
D

Re: Format a single cell in a Table (today) - Is this approach sensible?

BI_Curious — Sun, 15 Mar 2020 18:21:52 GMT

Ok, well, although I didn't intend to be controversial I can see that there are competing views here.

I started coding in around 1978 on a Commodore PET and later progressed to a "BEEB". Both had very little memory and efficient coding (aka "Elegant" coding) has been my mantra ever since. I get pleasure from the petty enjoyment of finding the least code to achieve a given task. HOWEVER, there is a lot to be said for creating code that can be scan read by team members / future maintainers.

In this example, I expect that most coders would be able to sight read:

Is Today = IF(DATEDIFF(TODAY(),Weight_Data[Weight_Date],DAY)=0,1,0)

more intuitively than:

Is Today = 1 * (TODAY() = Weight_Data[Weight_Date])

If for no other reason than the 2nd option relies on an understanding that False * 1 evaluates to 0 whereas True * 1 evaluates to 1 - "perhaps" not an instantly obvious detail.

But, getting back to my code, Anonymous you mention that, "A calculated column is a column in a model table" , and I accept that, but I have to ask, if in my case you are saying that I should change the [Is Today] calculation from being a measure into a calculated column or are you stating that [Is Today] is a calculated column?

Incidentally, great heads-up about the ability to use a calculated column in a slicer where a measure can't be - Hadn't twigged that, but see it's truth.

Every day's a school day 😀

Re: Format a single cell in a Table (today) - Is this approach sensible?

BI_Curious — Sun, 15 Mar 2020 18:35:01 GMT

I have just spent a fair amount of time drafting a response both to the question of code length and a supplementary question about my original code but having posted it, i got:

Not sure what I did to incur the wrath of the red pen brigade but just wanted you guys to know that I wasn't ignoring you ...

Re: Format a single cell in a Table (today) - Is this approach sensible?

Greg_Deckler — Sun, 15 Mar 2020 19:09:46 GMT

BI_Curious - Usually that only happens when you post things like 30 seconds apart or something like that. Odd.

Re: Format a single cell in a Table (today) - Is this approach sensible?

BI_Curious — Mon, 16 Mar 2020 19:11:43 GMT

Anonymous Greg_Deckler

Hi Guys,

Just a note to say that the admins have restored my "spam" comment ...😎

Re: Format a single cell in a Table (today) - Is this approach sensible?

Anonymous — Mon, 16 Mar 2020 22:21:15 GMT

Hi there.

[Is Today] cannot be a measure in the current state because it does not use any kind of aggregation. The formula I gave you cannot therefore be a measure.

But, of course, you can create a measure that will work the same on the date level but you have to define what it should return for any other level, for instance a month.

Best
D

Best
D