Forum Discussion
Using different calculations based on Column Value with switch/if loop
Hi rachelb123,
Thank you for clarification.
you can follow below approach.
create tool dimenssion table
ToolList =
DATATABLE(
"ToolName", STRING,
{
{"A"},
{"B"},
{"C"},
{"D"},
{"E"}
}
)Create Relationship
-
Relate
ToolList[ToolName]→UsageRateTable[ToolName]. -
Use ToolList[ToolName] in your matrix (instead of from
UsageRateTable).
Now write below measure
TotalWorkingDays = 63 // or dynamic from a calendar table later
Usage Rate (%) =
VAR _tool = SELECTEDVALUE('ToolList'[ToolName])
VAR _usageDays =
CALCULATE(
DISTINCTCOUNT('UsageRateTable'[UseDate]),
KEEPFILTERS(VALUES('ToolList'[ToolName]))
)
RETURN
SWITCH(
TRUE(),
_tool IN {"D","E"}, IF(_usageDays > 0, 1, 0),
DIVIDE(_usageDays, [TotalWorkingDays], 0)
) * 100-
Usage Rate (%) =
VAR Tool = SELECTEDVALUE(UsageRateTable[ToolName])
VAR DaysUsed = CALCULATE(DISTINCTCOUNT(UsageRateTable[UseDate]), UsageRateTable[ToolName] = Tool)
VAR UsedOnce = CALCULATE(COUNTROWS(UsageRateTable), UsageRateTable[ToolName] = Tool)
RETURN
SWITCH(
TRUE(),
Tool IN {"A", "B", "C"}, DIVIDE(DaysUsed, 63) * 100,
Tool IN {"D", "E"}, IF(UsedOnce > 0, 100, 0),
BLANK()
)
This handles all five tools with correct logic—A/B/C by usage days, D/E as binary flags. Clean, scalable, and ready for matrix visuals.
Shahid12523 Thank you for your response. In this solution if the data isn't present then it needs to be 0% but in your solution it removes the entire toolname from the matrix.
