Using different calculations based on Column Value with switch/if loop

[rachelb123]

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 :

Usage Days = DISTINCTCOUNT('Usage RateOpEx'[UseDate])

Usage Rate (%) = 'UsageRateTable'[Usage Days]/64

There were my tries --

AnotherUsage = IF('Usage RateOpEx'[ToolName] = "D" || 'Usage RateOpEx'[ToolName] = "E", 'UsageRateTable'[Usage Days]/1, 'UsageRateTable'[Usage Days]/64)

TestUsage = SWITCH(TRUE,'UsageRateTable'[Usage Days] = "Cycle Time Analysis Tool" ,'UsageRateTable'[Usage Days]/1,'Usage RateTable'[Usage Days] = "Cycle Time Risk Report DRC", 'Usage RateTable'[Usage Days]/1, 'Usage RateTable'[Usage Days]/64 )

I'm not sure how to do this by seperating out tools I keep getting no results. Thanks in advance for any help you can offer.

[Shahid12523]

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.

[rachelb123]

@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. 

[FBergamaschi]

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

[rachelb123]

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%

[grazitti_sapna]

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!

[rachelb123]

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.

[grazitti_sapna · Accepted solution]

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