cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValeriaBreve
Helper IV
Helper IV

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.

 

Thanks a lot in advance for your help,

Kind regards

Valeria

1 ACCEPTED SOLUTION

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

View solution in original post

9 REPLIES 9
ValeriaBreve
Helper IV
Helper IV

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

ValeriaBreve_0-1663951200526.png

ValeriaBreve_1-1663951218285.png

 

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

 

 

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

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!

 

 

 

ValeriaBreve_0-1664813007319.png

 

 

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.

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

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!

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.

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!

johnt75
Super User
Super User

can you share a PBIX with any confidential info removed ?

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors