Forum Discussion
Using different calculations based on Column Value with switch/if loop
I am trying to find usage rate = number of days used in quarter/total days in quarter for 5 tools. Some have a different specification for this calculation as stated below:
Tool A,B,C = number of days used/ total working days in quarter
Tool D & E = If used even once a quarter then 100% if no lines present with this toolname then 0%
Here is the main table - UsageRateTable
| ToolName | UserName | UserEmail | UseDate |
A | user1 | n | 7/1/2025 |
| A | user3 | n | 7/2/2025 |
| B | user2 | n | 7/1/2025 |
| C | user3 | n | 7/1/2025 |
| B | user1 | n | 7/2/2025 |
| A | user2 | n | 7/3/2025 |
| C | user3 | n | 7/4/2025 |
| C | user2 | n | 7/4/2025 |
D | user 1 | n | 7/2/2025 |
| D | user 1 | n | 7/4/2025 |
| B | user2 | n | 7/1/2025 |
And I would like to calculate usage rate with the formula = (days the app was used/total number of days in a quarter)*100.
Usage Rate for Tool A = 3/63
Usage Rate for B = 2/63
Usage Rate for C = 2/63
Usage Rate for D = 100%
Usaged Rate for E = 0%
This is how I currently calculate before Tool D and E were added:
Measure :
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-
7 Replies
Hi rachelb123,
You should create a separate calendar table and mark working days. But since you already mentioned 63 or 64 days, we can hardcode for now:
TotalWorkingDays = 63 // replace with dynamic calc if needed
Now Create a measure
Usage Rate (%) =
VAR _tool = SELECTEDVALUE('UsageRateTable'[ToolName])
VAR _usageDays = CALCULATE(
DISTINCTCOUNT('UsageRateTable'[UseDate]),
ALLEXCEPT('UsageRateTable', 'UsageRateTable'[ToolName])
)
RETURN
SWITCH(
TRUE(),
_tool IN {"D","E"},
IF(_usageDays > 0, 1, 0), -- 100% if used once, else 0%
DIVIDE(_usageDays, 63, 0) -- normal calc for A, B, C
) * 100🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!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-
Hi rachelb123,
Just to be sure:
1 test usage, usage rate etc you mention in your post are just different attempato to solve the sane issue?
2 why the 64 at the denominator? Should not that be the nr of days of the quarter? Which might or not be 64 if we speaker about working days...
3 we are speaking about a measure or a column to create in DAX? I assume a measure but pls confirm
Finally, can you show an image of the visual you want to apply this thing to?
Thanks
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FBergamaschi,
1. Usage Days and Usage Rate are the original measure I used before Tool D and E were added to my dashboard. This worked because all the tools in the original table just used the basic formula stated above but now with this new addition there is a new way to calculate those. The Another Usage and Test Usage is my attempt at trying to include tool D and E into the calculation. They are both measures.
2. It is 64 because it excludes weekends and holidays.
3. Im very new at PowerBI and I dont know if having it as a measure/column is easier. Due to personal data I can't share a screenshot but this is what I want the matrix to look like-
ToolName Usage Rate (%) A 4.76% B 3.17% C 3.17% D 100% E 0%
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.
