Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have the following example data:
I want to add a column that should return the Value for a certain ID when it was first recorded as indicated by Period. I used the following Query to obtain the result: FirstValue=IF(CALCULATE(MIN(table[Period]),ALLEXCEPT(table,table[ID]))=table[Period], table[Value], BLANK())
This gave me the result as indicated below.
The problem with these result are that the Query did not take into account that some Values are empty for the Period when it was first recorded and it therefore returns BLANK() fields for that certain ID and Period. What I want is to return the FirstValue for the first Period where Value is not blank. As the example below shows:
Any ideas on how to do this?
Solved! Go to Solution.
Hi @bamba98 ,
Please try this:
Column =
VAR a =
MINX (
FILTER (
ALL ( 'Table' ),
EARLIER ( 'Table'[Period] ) >= 'Table'[Period]
&& EARLIER ( 'Table'[ID] ) = 'Table'[ID]
&& 'Table'[Value] <> BLANK ()
),
'Table'[Period]
)
VAR b =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Period] = a
&& EARLIER ( 'Table'[ID] ) = 'Table'[ID]
)
)
RETURN
IF ( 'Table'[Period] = a, b, BLANK () )
Hi @bamba98 ,
Please try this:
Column =
VAR a =
MINX (
FILTER (
ALL ( 'Table' ),
EARLIER ( 'Table'[Period] ) >= 'Table'[Period]
&& EARLIER ( 'Table'[ID] ) = 'Table'[ID]
&& 'Table'[Value] <> BLANK ()
),
'Table'[Period]
)
VAR b =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Period] = a
&& EARLIER ( 'Table'[ID] ) = 'Table'[ID]
)
)
RETURN
IF ( 'Table'[Period] = a, b, BLANK () )
@bamba98 , Try a new column like
calculate(firstnonbank([Value],blank()), filter(Table, [ID] = earlier([ID])))
Hi @bamba98 ,
Try something as below:
Modify the above DAX using your table name and column name.
Thanks,
Pragati
hi @Pragati11 and @amitchandak
I don't see that you took the period into account. The dataset that I have is not ordered.
Or am I missing something?
@bamba98 , Data you shared, is not making it clear.
Can you share sample data and sample output in table format?
I have tried to improve my explanation. Let me know if it is still not clear.
@bamba98 , Try like
if([period]= calculate(firstnonbank([period],blank()), filter(Table, [ID] = earlier([ID]))),[Value],blank())
@Pragati11 It should take both ID and Period into account. My Query is wrong as it does not take into account that the field for value is empty for the mimimum period. It should therefore go to the next minimum poriod and return that value if the field for value is not empty.
Hi @bamba98 ,
Do you want both ID and PERIOD to be taken into account or just ID or just PERIOD?
Because your query is not clear. I have shown the result you are trying to get based on your screenshot.
Thanks,
Pragati
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |