Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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) =
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:
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
Solved! Go to 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 )
)
)
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) =
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!
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!
can you share a PBIX with any confidential info removed ?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
16 |
User | Count |
---|---|
29 | |
27 | |
20 | |
15 | |
14 |