Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a requirement to showcase on a table employees who went from contractor to full time.
How can I achieve this? Is there a DAX to create a count or highlight those names which converted?
Name | Hire Date | Status |
Mary Jane | 1/1/2019 | Contractor |
Mary Jane | 1/1/2020 | FTE |
Gary Smith | 11/1/2018 | Contractor |
Bob Ferguson | 12/1/2016 | Contractor |
Bob Ferguson | 06/01/2018 | FTE |
Solved! Go to Solution.
Hi @Anonymous ,
Create a measure like below and add it to visual filter then configure the condition formatting like below.
Measure = CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Name]))
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Create a measure like below and add it to visual filter then configure the condition formatting like below.
Measure = CALCULATE(DISTINCTCOUNT('Table'[Status]),ALLEXCEPT('Table','Table'[Name]))
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
You can create a new table:
Converted =
FILTER (
DISTINCT ( Table1[Name] );
VAR LastDate_ =
CALCULATE ( MAX ( Table1[Hire Date] ) )
VAR IsFTEOnLastDate_ =
CALCULATE (
COUNT ( Table1[Hire Date] );
Table1[Hire Date] = LastDate_;
Table1[Status] = "FTE"
) > 0
VAR WasPreviouslyContractor_ =
CALCULATE (
COUNT ( Table1[Hire Date] );
Table1[Hire Date] <= LastDate_;
Table1[Status] = "Contractor"
) > 0
RETURN
AND ( IsFTEOnLastDate_; WasPreviouslyContractor_ )
)
where Table1 is the table you show
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
You need to use the name of your table in lieu of Table1 and perhaps commas instead of ";" depending on your locale
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
@Anonymous
Try this measure
contractortofulltime =
var _isContractor= CALCULATE(COUNT('Table'[Status]),'Table'[Status]="Contractor")
var _isFulltime= CALCULATE(COUNT('Table'[Status]),'Table'[Status]="Full Time")
var _contractortofulltime = IF(_isContractor>0&&_isFulltime>0,"Yes","No")
return _contractortofulltime
Now you can apply this measure in visual level filter ( is Yes ).
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂