cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## Issue with measure

Hello,

I have a PowerBI report which is quite simple: 1 transactional table + a date table. The transactional table has data since June 2022 - the date table has dates since 1/1/2022.

The 2 are connected via the date column (I made sure it is a pure "date" in the transactional table - no date).

In the transactional table, I have a calculated column:

Total Amount (kg) =

if ('Transactional Table'[CONFIRMED_AMOUNT]=0,'Transactional Table'[SCHEDULED_AMOUNT],'Transactional Table'[CONFIRMED_AMOUNT])

which works.

When I create a (visual) table with the Datetable[Date] and this column, I see correctly the dates as of June 2022 (first entry in the transaction table), and the related amount.

Then I built a measure using this calculated column:

Total Consumption (kg) =
CALCULATE (
sum ('Transactional Table'[Total Amount (kg)]),
DATESBETWEEN (
DateTable[Date],
[StartDate],
[EndDate]
)
)
where End Date and Start Date are calculated via parameters.

Can somebody explain to me why, when I add this into the (visual) table with the DateTable[Date] and the calculated column [Total Amount (kg)], suddenly the table contains dates as of Jan 1st 2022, whith data for both the calculated column [Total Amount (kg)] and measure [Total Consumption (kg)], where there are no data for these dates? I can't understand this behaviour.

Kind regards

Valeria

1 ACCEPTED SOLUTION
Super User

I don't know what is going on with the calculated column, but you can get the daily figures by amending your calculation item to be

``````VAR numDaysBefore =
SELECTEDVALUE ( 'Days Before Today'[Days Before Today] )
VAR numDaysAfter =
SELECTEDVALUE ( 'Days After Today'[Days After Today] )
VAR baseDate =
TODAY ()
VAR startDate = baseDate - numDaysBefore
VAR endDate = baseDate + numDaysAfter
VAR datesToUse = DATESBETWEEN (
DateTable[Date],
startDate,
endDate
)
VAR selectedDate = SELECTEDVALUE( DateTable[Date])
RETURN
IF(
selectedDate IN datesToUse,
CALCULATE(
SELECTEDMEASURE(),
KEEPFILTERS( datesToUse )
)
)``````
9 REPLIES 9
Post Patron

Hi John,

I have tried my best to replicate the file.

So, I cannot replicate the issue I have with the calculated column in the original file - which I cannot share as it is confidential. It looks the same, format is number, no blanks, but PowerBI will not allow me to do any aggregation with it. When I put this calculated column in a table, instead of having the sum as per specified summarization, I get discreet numbers... I don't understand what is going on. If you have any hints about what I can check, it would be much appreciated!

Calculated Column:

Total Amount (kg) =

if ('Transactional Table'[CONFIRMED_AMOUNT]=0,'Transactional Table'[SCHEDULED_AMOUNT],'Transactional Table'[CONFIRMED_AMOUNT])

... no summarizing possible

However, even with the mock file, I can't get what I want  which is actually what you suggested to me on another post (Solved: Re: Use What-If parameter for selecting a date int... - Microsoft Power BI Community). I wanted to be able to let the users filter for dates through the use of parameter slicers (Days Before Today/ Days After Today), as you taught me . I have tried to apply the calculation group, but it does not work... the measure calculates for every date, so I cannot filter out what's = 0. What am I doing wrong?

File is on https://we.tl/t-tjWRmXRCAU

Thanks a lot!

Kind regards

Valeria

Super User

I don't know what is going on with the calculated column, but you can get the daily figures by amending your calculation item to be

``````VAR numDaysBefore =
SELECTEDVALUE ( 'Days Before Today'[Days Before Today] )
VAR numDaysAfter =
SELECTEDVALUE ( 'Days After Today'[Days After Today] )
VAR baseDate =
TODAY ()
VAR startDate = baseDate - numDaysBefore
VAR endDate = baseDate + numDaysAfter
VAR datesToUse = DATESBETWEEN (
DateTable[Date],
startDate,
endDate
)
VAR selectedDate = SELECTEDVALUE( DateTable[Date])
RETURN
IF(
selectedDate IN datesToUse,
CALCULATE(
SELECTEDMEASURE(),
KEEPFILTERS( datesToUse )
)
)``````
Post Patron

Hi John, I just discussed with a Microsoft MVP who helped me explain why the calculated column implicit measures stopped working. This is actually a feature in calculation groups - implicit measures are discouraged whenever you have a calculation group created. So Tabular editor sets your Option for Discourage Implicit Measures to "True" and the implicit column aggregations will disappear, as well as the "sigma" sign next to them to signify that implicit aggregations are gone.

So finally I understand what's happening now!

Super User

That's really interesting. I didn't know that option existed, but I'm going to start using it so that I don't have to manually go through and turn off the implicit measures. Thanks for the update.

Post Patron

you're welcome! Thanks to Tommy Puglia Puglia BI Consulting - Power BI for helping me on this one 🙂

Post Patron

Thanks! This works! Can you please explain to me the why the previous calculation was not working? For me to understand so that I can apply them correctly in the future. Thanks! 🙂

For the other issue, I raised a ticket with MSFT as I have no idea what is going on. I will share the explanation if interesting for the community. Thanks again!

Super User

Your previous calculation was completely overriding any filters on the date table, so it wasn't able to show values for a given date. By adding KEEPFILTERS you can combine the selected dates filter with the filter for a given date which comes from the visual.

Post Patron

Thanks! My eyes can't se the actual difference between this case and the one from my earlier case - why was this calculation working well in the other report. I will need to better look into it. Anyway, for the aggreation issue, it actually does show up also in the .pbix I sent, I discovered it starts at the moment that you apply the calculation item to the report - from the tabular editor - without even actually using it. You see the "sigma" for the number columns go away, and if you try to add a number column to a table, PowerBI does not aggregate it any longer (it does keep it aggregated if it was before). Measures still work correctly. I have submitted a ticket with MSFT.

Thanks againf or all your help!

Super User

can you share a PBIX with any confidential info removed ?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors