Forum Discussion
Solved
How to keep filters on table visualization when adding a new measure
Hi all, I have a problem where when I add a measure to a table visualization, the slicers affecting the visualization seem to stop working. I have my data in a snowflake schema. I am illus...
Hi dh76_PBI
Yes, this is indeed to do with zeros, specifically that BLANK is equal to zero, so that 1 - BLANK evaluates to 1 or 100%.
You could rewrite your measure a few ways to avoid returning a result when the ratio of the two measures is blank (I would probably prefer the first):
KPI Penalty Open = VAR PenaltyOpen = [Sum Penalty Open] VAR CountOpen = [Count Open] RETURN DIVIDE ( CountOpen - PenaltyOpen, CountOpen )or
KPI Penalty Open = IF ( NOT ISEMPTY ( 'Fact Table' ), 1 - DIVIDE ( [Sum Penalty Open], [Count Open] ) )or
KPI Penalty Open = VAR PenaltyOpen = [Sum Penalty Open] VAR CountOpen = [Count Open] VAR Ratio = DIVIDE ( PenaltyOpen, CountOpen ) RETURN IF ( NOT ISBLANK ( Ratio ), 1 - Ratio )Regards,
Owen
OwenAuger
Super User
Super UserHi dh76_PBI
Yes, this is indeed to do with zeros, specifically that BLANK is equal to zero, so that 1 - BLANK evaluates to 1 or 100%.
You could rewrite your measure a few ways to avoid returning a result when the ratio of the two measures is blank (I would probably prefer the first):
KPI Penalty Open =
VAR PenaltyOpen =
[Sum Penalty Open]
VAR CountOpen =
[Count Open]
RETURN
DIVIDE ( CountOpen - PenaltyOpen, CountOpen )
or
KPI Penalty Open =
IF (
NOT ISEMPTY ( 'Fact Table' ),
1 - DIVIDE ( [Sum Penalty Open], [Count Open] )
)or
KPI Penalty Open =
VAR PenaltyOpen =
[Sum Penalty Open]
VAR CountOpen =
[Count Open]
VAR Ratio = DIVIDE ( PenaltyOpen, CountOpen )
RETURN
IF (
NOT ISBLANK ( Ratio ),
1 - Ratio
)Regards,
Owen
