Forum Discussion
Column based filtering
I have a dataset containing the below column
Name | SickLeave (h)| Holliday (h)| Vacation (h) |WorkingHour (h) | Month
in card visual would like to show the Absence ratio, and the slicer filter to choose the type of absence
3 Replies
Sounds like the data isn't formatted for your needs correctly. In order to slice by the type of absence you will need one column, maybe named something like absenceType, that has the values "Holiday", "Vacation", "SickLeave", etc. You may need to first pivot your data to obtain this type of structure. Then you can create the slicer.
Once you have the slicer allowing you to choose the type of leave you can use the keyword SELECTEDVALUE in your measure to filter for the specific types of absences that are selected in the slicer to calculate Absence ratio.
Without further detail on exactly how you define "Absence ratio" it is difficult to suggest how the measure may look.
Hope that helps point you in the right direction though.
absence ratio= SickLeave (h)+ Holliday (h)+ Vacation (h) / WorkingHour
OK, so what would the slicer do then?
This absence ratio can be written as a DAX Measure like this:
Absence Ratio =
DIVIDE(
SUM('tableName'[SickLeave(h)]) + SUM('tableName'[Holliday(h)]) + SUM('tableName'[Vacation (h)]),'tableName'[WorkingHour],
0
)
Note, the use of DIVIDE is better than the / operator because the third argument specifies what to return in a divide-by-zero scenario. In this instance I have set the return value to 0 if that happens.
This measure could be sliced by Name in any sort of query context from a visualization. For example, you could set the rows in a matrix to Name and the Values in the matrix to Absence Ratio and see a list of ratios for each individual.
