Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I will try to simplify at most to undermine understanding.
I have a range of dates, for example from January 1st to March 1st.
I have a table with a DATA column and another X column with a value, for example: on March 01 and 02 the value of column X is 1, on 03 and 04 the value of column x is 5, on 05 and 06 the value returns to 1.
I need to make a count of how many days the value of X occurred, but with each new count I should not consider the previous one, example in my filter I selected day 2 (on that day the value of X is 1), my result will be 2 days, because I have the day 01 and 02 with the value 1, when I select the day 04 the value of X will be 5 then my result will be 2 because I have the value 5 on the day 03 and 04, until now all right, the problem is when I select the day 06 (on that day the value of X is 1 again) then the value that is being returned is 4 because it also considers the days 01 and 02, the need is not to count more days 01 and 02 and counted only 05 and 06, because between day 02 and 05 I had different values from 1 to X.
If anyone has any ideas.
Solved! Go to Solution.
Hi, @Thcferreira
You need to create a index column in query editor first
Then you can try to create calculated columns as below:
color flag =
VAR value1 =
'COVID'[TRANSMISSIBILIDADE]
RETURN
SWITCH (
TRUE (),
value1 > 1.5, "red",
value1 >= 1
&& value1 <= 1.5, "orange",
value1 < 1, "green"
)
ChangedStatue =
VAR pre =
CALCULATE (
VALUES ( COVID[color flag] ),
FILTER ( ALL ( COVID ), 'COVID'[Index] = EARLIER ( COVID[Index] ) - 1 )
)
RETURN
IF ( ISBLANK ( pre ) || pre = 'COVID'[color flag],"unchanged","changed" )
Counting days =
VAR _max =
CALCULATE (
MAX ( 'COVID'[Index] ),
FILTER (
'COVID',
'COVID'[Index] <= EARLIER ( 'COVID'[Index] )
&& 'COVID'[ChangedStatue] = "changed"
)
)
RETURN
IF ( ISBLANK ( _max ), 'COVID'[Index], 'COVID'[Index] - _max + 1 )
The result will show as below:
For more details,please check the attached file.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry, I'm new to powerbi
I tried to simplify the example for a better understanding, now I'm providing more information, I couldn't attach the pbix.
this need is for a map that measures the transmissibility of each state during the covid pandemic.
so the need to know how many days each state stayed in a certain transmissibility range (this range is defined by the transmissibility) and keep track of the changes in ranges, so I cannot accumulate the values, every time I change the range to present a new value.
Here is an example of the table I am using: State, transmissibility (Rt) and the date of a table called covid
The rules of the range are as follows:
transmissibilidade > 1,5 - red
transmissibilidade >=1 e <=1,5 – orange
transmissibilidade <1 - green
On 05/30/2020 when the range is above 1.5 I have 54 days that I stayed in that range
On 05/31/2020 my range is no longer above 1.5 but <= 1.5 so it changed color
On 06/14/2020 my range returned to 1.5 and then he should start counting the days from scratch again, but he is also counting the days of that first period (05/30/2020 from the first image).
So what's the need? Let him count the days for each track, but when there is a change of track, for example I stayed 2 days in the range> 1.5 then I moved to the range <= 1.5 and then I went back to the range> 1.5 I must count those 2 days that I had stayed in this range in the count.
@Thcferreira , it seems more like cumulative
example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.
Is it possible to share a sample data . The explanation is not giving the context .
Proud to be a Super User!
Hi, @Thcferreira
You need to create a index column in query editor first
Then you can try to create calculated columns as below:
color flag =
VAR value1 =
'COVID'[TRANSMISSIBILIDADE]
RETURN
SWITCH (
TRUE (),
value1 > 1.5, "red",
value1 >= 1
&& value1 <= 1.5, "orange",
value1 < 1, "green"
)
ChangedStatue =
VAR pre =
CALCULATE (
VALUES ( COVID[color flag] ),
FILTER ( ALL ( COVID ), 'COVID'[Index] = EARLIER ( COVID[Index] ) - 1 )
)
RETURN
IF ( ISBLANK ( pre ) || pre = 'COVID'[color flag],"unchanged","changed" )
Counting days =
VAR _max =
CALCULATE (
MAX ( 'COVID'[Index] ),
FILTER (
'COVID',
'COVID'[Index] <= EARLIER ( 'COVID'[Index] )
&& 'COVID'[ChangedStatue] = "changed"
)
)
RETURN
IF ( ISBLANK ( _max ), 'COVID'[Index], 'COVID'[Index] - _max + 1 )
The result will show as below:
For more details,please check the attached file.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, now I'm having trouble calculating the days.
Hi, @Thcferreira
The formula I provided is a calculated column ,rather than a measure...
You can check it in my attached pbix file.
Best Regards,
Community Support Team _ Eason
Hello, then I realized my mistake.
I managed to solve using a measure, it was as follows:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |