Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a database (simplified model below) where I have the following columns:
I need to group of the range of consecutive days where the "Variable" is the same and to show min / max values.
See example below, each colour represents a new range (variable changed from 1 to 2, and from 2 to 1).
OBS: The "Index" column is not available in my database, I inserted in the Excel table for demonstration only.
Is there a possible way to do that via DAX?
Hi @Anonymous ,
Please firstly add a Index column in Power Query:
Then create columns:
Flag =
var _pre= CALCULATE(MAX('Table'[VARIABLE]),FILTER('Table',[MACHINE]=EARLIER('Table'[MACHINE]) && [Index]=EARLIER('Table'[Index])-1))
return IF([VARIABLE]<>_pre,[Index])
Filled Flag = MAXX(FILTER('Table',[MACHINE]=EARLIER('Table'[MACHINE]) && [Index]<=EARLIER('Table'[Index])),[Flag])
Rank(Grouped Index) = RANKX(FILTER('Table',[MACHINE]=EARLIER('Table'[MACHINE])),[Filled Flag],,ASC,Dense)
Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello!
Can you please take a look at the sample of the file, with the exact column names that I am using, I don't know why I'm not able to use the formula as you shared with me.
In the calculated columns, after the =EARLIER the errors start to appear.
MACHINE = MCH_CODE
VARIABLE = REMARK (3 options: 7TC, 7TF, 7TJ), these are the working sites, no math is involved with this column.
REG_DATE = Date + Time of the registry
RECORDED_VALUE = Value
Link below (can't attach files directly)
https://www.dropbox.com/s/0zbcjbn253vg3vs/PBI%20Sample%20FIle.pbix?dl=0
Thanks in advance for your help!
Hi @Anonymous
Just create measures with simple MIN/MAX of each column
Hello @tamerj1 , thanks for your time.
I should have explained better, but when I create simple measures with MIN/MAX I am going to have as an outcome the entire dataset and I need the MIN/MAX for the "Index" (range of consecutive days).
Please see picture below:
Do you already have the index column or you want to create it?
I need to create it.
Sorry @Anonymous I just saw your reply.
Yes the index column can be created by dividing the rank over 3 (the group size) We just need to round it up to conver to ineger.
INDEX =
VAR GroupSize = 3
VAR Ranking =
RANKX (
CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[MACHINE] ) ),
Data[DATE],,
ASC
)
VAR Result =
ROUNDUP ( DIVIDE ( Ranking, GroupSize ), 0 )
RETURN
Result
Here is a sample file for your reference https://www.dropbox.com/t/gUbW1KY31MOVROXc
Hello @tamerj1 I really appreciate that you're taking your time to help me but unfornately the solution is not as expected. There is only 1 machine "A" in the example. I wrote only 3 lines per "index" but there can be as many as possible for each condition so there is no rule for that also. Any ideas?
OBS: There are several records for each machine daily...
Hi @Anonymous
Regarding the error, this code is for a calculated column not a measure.
Regarding the group size actually I anticipated that you would say that. And my question is: if not fixed then based on what?
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
37 | |
21 |