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

[BI_Curious]

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:

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

Details:

1. Create 1st measure: 

Weight_Date = max('Weight_Data'[Date])​

1. Create 2nd measure:

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

1. 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!

[Anonymous]

[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

[BI_Curious]

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

[Greg_Deckler]

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

[Anonymous]

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

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

Best
D

[BI_Curious]

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

[BI_Curious]

@Anonymous @Greg_Deckler 

Hi Guys,

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

[Greg_Deckler]

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

[BI_Curious]

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 😀

[Anonymous]

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

[Anonymous]

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

[Greg_Deckler]

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

[BI_Curious]

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