Forum Discussion

lfholland's avatar
lfholland
Advocate I
1 year ago
Solved

Alternative to SWITCH ()?

My row level security access table has a one to many relationship with my fact table.  In this particular chart, when I filter on one of the roles, it filters the data associated with the measures correctly, but leaves the all the dimension items regardless of relation.  Filtering works correctly on all other charts/graphs in the report except for this one.  I think it is associated with the fact that the two metrics are calculated using the SWITCH () function - that's the only difference I can see.  Any ideas on a possible solution?

Formula for Net Sales

Net Sales = SWITCH(TRUE(),
MAX('Period Column Table'[Period]) = "This Month", FORMAT('Summary table Calculations'[Net Sales This Month],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "TM - 1", FORMAT('Summary table Calculations'[Net Sales TM - 1],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "TM - 2", FORMAT('Summary table Calculations'[Net Sales TM - 2],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "TM - 3", FORMAT('Summary table Calculations'[Net Sales TM - 3],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "TM - 4", FORMAT('Summary table Calculations'[Net Sales TM - 4],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "TM - 5", FORMAT('Summary table Calculations'[Net Sales TM - 5],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "CY YTD", FORMAT([TY YTD Net Sales],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "PY YTD", FORMAT([LY YTD Net Sales],"$#,##0;($#,##0)"),
MAX('Period Column Table'[Period]) = "YTD Var", FORMAT(MyMeasures[YOY Growth],"Percent")
)
 
Image of dashboard when a filter is applied:
 

 

Need Help
  • lfholland's avatar
    lfholland
    1 year ago

    I ended up brining the role into the fact table since it was a one (access) to many (fact).  It works that way but I would think there's a better way.  And it is definately the SWITCH function.  I tried the table with other elements and it worked fine until I added the metric using SWITCH.

7 Replies

  • lfholland's avatar
    lfholland
    Advocate I
    1 year ago

    I ended up brining the role into the fact table since it was a one (access) to many (fact).  It works that way but I would think there's a better way.  And it is definately the SWITCH function.  I tried the table with other elements and it worked fine until I added the metric using SWITCH.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi lfholland 

    Possibly due to the use of FORMAT.  This function returns a text and still applies formatting to a blank value but of course returns a white space. Try using dynamic format strings instead.

     

    IF (
    MAX ( 'Period Column Table'[Period] ) = "YTD Var",
    "Percent",
    "$#,##0;($#,##0)"
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi lfholland,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to danextian  and Greg_Deckler  for prompt and helpful responses.

     

    Just following up to see if the responses provided by community members were helpful in addressing the issue.

    If one of the responses helped resolve your query, please consider marking it as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar