Forum Discussion

rachelb123's avatar
rachelb123
Helper I
11 months ago
Solved

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 da...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    11 months ago

    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

     

grazitti_sapna's avatar
grazitti_sapna
Super User
11 months ago

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's avatar
    rachelb123
    Helper I
    11 months ago

    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's avatar
      grazitti_sapna
      Super User
      11 months ago

      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