Forum Discussion

dh76_PBI's avatar
dh76_PBI
Frequent Visitor
3 years ago
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...
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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

IIPowerBlog's avatar
IIPowerBlog
Icon for Helper I rankHelper I
3 years ago

Hi dh76_PBI  while I fully agree with OwenAuger  and the solution, maybe this is simpler than expected:

Try the simple following steps: 

First:

1a. Your Lookup Table should include the ID column of the Location table 

OR

1b. Your Location Table should include the Region ID

Have you related these two tables correctly? The ID column of the Location table has to be your KEY 

 

Then (optinal if the first doesnt work out!) 

1. Instead of the COUNT measures, use COUNT directly on the fields 'Open' and 'Penalty Open' on the Visualization of your table. 

2. For your KPI calculation create a calculated field.

3. Remove the measures from your table and add the Calculated Field from step 2. 

 

Does this work?