Add new column with "where" clause

[filipe]

Hi all,

i'm newbie with DAX so your help would be really great.

I have two tables:

TableA: Energy Readings table (once a day I record total Energy consumption per energy counter). Columns: ReadingDate, CounterID, Energy

and than I have the Clients Table. Each client may have one or several counters associated (depends on room size...) and StartDate and EndDate. In this Table i'll keep record of old clients (to historize energy consumption), so the relation between this two tables is many-to-many:

...My problem here is that i need to keep track of the energy consumption by client.

My idea was to add a new Column to ReadingsTable with the actual client related to the reading date.

It would be something like this:

..any ideas on how to have the new column? The logic would be to add Client name with the related client from Client table according to the dates.

Thank you in advance!

[v-jiascu-msft · Accepted solution]

Hi @filipe,

It seems the Date and the CounterID can determine a unique row. I would suggest you add such a column. No matter who is using that room, the history can be tracked. It could be like below.

ID =
FORMAT ( [ReadingDate], "YYYYMMDD" ) & [CounterID]

Best Regards,

[filipe]

Thank you very much!