Alternative to SWITCH ()?

[lfholland]

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:

[Aburar_123]

Hi @lfholland ,

Please unselect "Show items with no data" option and check,

[lfholland]

Unfortunately this is already deselected.

[v-pgoloju]

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

[danextian]

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)"
)

[lfholland · Accepted solution]

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.

[Greg_Deckler]

@lfholland Have you checked this box on the relationship?

[lfholland]

I did and that didn't resolve it.