Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table here
All I want to do is this --
I want a new calculated column call ranking and a combined column of the values.
The logic here really is a grouping of EMPLOYEE, then the TYPE, then somehow a smart ranking of the ranking column to get at the combined value.
I tried
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Those are for creating calculated columns.
Ranking CC =
VAR _addflag =
FILTER (
ADDCOLUMNS (
FILTER ( Data, Data[Employee] = EARLIER ( Data[Employee] ) ),
"@flag",
SWITCH (
TRUE (),
VAR _mindateofemp =
MINX (
FILTER ( Data, Data[Employee] = EARLIER ( Data[Employee] ) ),
Data[As of date]
)
RETURN
Data[As of date] = _mindateofemp, 1,
VAR _previousdate =
MAXX (
FILTER (
Data,
Data[Employee] = EARLIER ( Data[Employee] )
&& Data[As of date] < EARLIER ( Data[As of date] )
),
Data[As of date]
)
VAR _previoustype =
MAXX (
FILTER (
Data,
Data[Employee] = EARLIER ( Data[Employee] )
&& Data[As of date] = _previousdate
),
Data[Type]
)
RETURN
Data[Type] = _previoustype, 0,
1
)
),
Data[As of date] <= EARLIER ( Data[As of date] )
)
RETURN
SUMX ( _addflag, [@flag] )
Combined CC =
COMBINEVALUES ( " ", Data[Type], Data[Ranking CC] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Those are for creating calculated columns.
Ranking CC =
VAR _addflag =
FILTER (
ADDCOLUMNS (
FILTER ( Data, Data[Employee] = EARLIER ( Data[Employee] ) ),
"@flag",
SWITCH (
TRUE (),
VAR _mindateofemp =
MINX (
FILTER ( Data, Data[Employee] = EARLIER ( Data[Employee] ) ),
Data[As of date]
)
RETURN
Data[As of date] = _mindateofemp, 1,
VAR _previousdate =
MAXX (
FILTER (
Data,
Data[Employee] = EARLIER ( Data[Employee] )
&& Data[As of date] < EARLIER ( Data[As of date] )
),
Data[As of date]
)
VAR _previoustype =
MAXX (
FILTER (
Data,
Data[Employee] = EARLIER ( Data[Employee] )
&& Data[As of date] = _previousdate
),
Data[Type]
)
RETURN
Data[Type] = _previoustype, 0,
1
)
),
Data[As of date] <= EARLIER ( Data[As of date] )
)
RETURN
SUMX ( _addflag, [@flag] )
Combined CC =
COMBINEVALUES ( " ", Data[Type], Data[Ranking CC] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
This worked, but is there an easier way? There must be an easier way - this is so much DAX to do something so simple. I really appreciate it, but I can't help but think there must be an easier way! 🙂
@peterhui50 This might help with the ranking:
https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520
The 2nd column is just:
Combined = CONCATENATE([Type],[Ranking])
or
Combined = [Type] & [Ranking]
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |