Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi everyone,
I'm having a trouble when I try to filldown some values. Everything works fine when I use my major calendar[Date] as the date column within the table, but when I try to use an "DD/MM" formated column, my filled rows doesn't work and goes blank (I don't know if that's related to multiple days/months from different years and that's why it keeps showing values from different years inside the same month from those different days).
Here's the result expected:
Here's the result I'm getting when using the "DD/MM" formated column:
Here below I share you my filldown measure:
IF(ISBLANK(AVERAGE(Table[Value])),
LASTNONBLANKVALUE(
FILTER(ALL('.dCalendar'[Date]),'.dCalendar'[Date]<=MAX('.dCalendar'[Date])),
CALCULATE(AVERAGE(Table[Value]))),
AVERAGE(Table[Value])
)
I already tried some solutions like using more then one relationship with the USERELATIONSHIP function and changing the LASTNONBLANKVALUE logic, but none of them worked.
Thanks in advance.
Hi @bubbledep ,
Please check that after you change the format, its data type is still a date type.
Please feel free to correct me and provide more information if I have misunderstood you!
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
The "DD/MM" column is text format yes.
Hi @bubbledep ,
Because the text format is not available for this part of the expression to be compared, it will result in a blank return:
'.dCalendar'[Date]<=MAX('.dCalendar'[Date])
Best regards,
Community Support Team_ Scott Chang
so, what is the solution?
Hi @bubbledep ,
Add an index column via power query to change the date in the expression to index.
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
How the Index column will work in this scenario? Could you provide info, please
Hi @sudhisami_azure ,
Go to the power query screen through transform data and click on Add column and it will add a column Index and then click on close and apply. and use the following expression:
IF(ISBLANK(AVERAGE(Table[Value])),
LASTNONBLANKVALUE(
FILTER(ALL('.dCalendar'[Index]),'.dCalendar'[Index]<=MAX('.dCalendar'[Index])),
CALCULATE(AVERAGE(Table[Value]))),
AVERAGE(Table[Value])
)
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I already have an IndexColumn which I made using RANKX, but the problem is that I can't relate my DD/MM column to sort by that IndexColumn or something like that. I need the DD/MM formated column as my x-axis, that's the goal.
Since I have multiples DD/MM's which can repeat in all year from my calendarTable, I can't sort my DD/MM formated column by any calendar[Date] or IndexColumns, because DD/MM can repeat tru years, meanwhile it's not allowed for calendar[Date] and IndexColumn to have repeated values.
In order to exemplify, I share below the IndexColumn that I already had, which works just fine to fill my logic:
IF(ISBLANK(AVERAGE(Table[Value])),
LASTNONBLANKVALUE(
FILTER(ALL('.dCalendar'[Index]),'.dCalendar'[Index]<=MAX('.dCalendar'[Index])),
CALCULATE(AVERAGE(Table[Value]))),
AVERAGE(Table[Value])
)
Also my other measures related to this visual (which I use calendar[Date]) doesn't break since my IndexColumn is related to my calendar[Date], and that's awesome. But my problem still, because I don't need to use an IndexColumn as my x-axis, I need to use a DD/MM formated column as my x-axis instead.
Hi @bubbledep ,
Put the DD/MM into X-axis and then use sort by column and select index in there to achieve your desired result.
Sort one column by another column in Power BI - Power BI | Microsoft Learn
Hope it helps!
Best regards,
Community Support Team_ Scott Chang
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
"Date_Total_Day" is my index column that I created using RANKX formula and my Date DD/MM column is formatted as "FORMAT(calendar[Date], "DD/MM")". But since I have repeated dates from different years (date 01/02 from years 2022, 2023 and 2024 for example), I'm unable to sort by my index column because can't have repeated values in there like in my Date DD/MM formated column.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
34 | |
25 | |
23 | |
23 |