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-
Hi grazitti_sapna, Thank you for your response, In the solution if the tool was never used it won't show up in the data but in the table it should still show as 0% since it wasn't present but your solution removes that tool altogether in the matrix.
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