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.
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]
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 |
---|---|
108 | |
95 | |
75 | |
63 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
66 |