Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago
Solved

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 con...
  • johnt75's avatar
    johnt75
    3 years ago

    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 )
		)
	)
ValeriaBreve's avatar
ValeriaBreve
Post Partisan
3 years ago

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

 

 

johnt75's avatar
johnt75
Super User
3 years ago

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 )
		)
	)
  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    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!

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      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.

      • ValeriaBreve's avatar
        ValeriaBreve
        Post Partisan
        3 years ago

        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!

  • ValeriaBreve's avatar
    ValeriaBreve
    Post Partisan
    3 years ago

    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!

     

     

     

     

     

    • johnt75's avatar
      johnt75
      Super User
      3 years ago

      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.