Create a table with a variable column dependant on slicers

[nicolasvargas]

Hello,

I have my main table, to which I apply several slicers to filter the data. After slicing, I would like to count the occurrence of certain events within an specified range. Something as the following table:

Lower Bound	Upper bound	Count
-1	-0.5	x
-0.5	-0.25	x
-0.25	-0.1	x
-0.1	0	x
0	0.1	x
0.1	0.25	x
0.25	0.5	x
0.5	1	x

Where the count is for all number of events between lower and upper bound.

For the moment I tried to create a new column with the following formula:

HitRate = 
VAR Hits = CALCULATE
(
    COUNT('Database'[Returns]),
    FILTER(
        'Database',
        'Database'[Returns] < 'Hit Rate Table'[UpperBound] &&
        'Database'[Returns] > 'Hit Rate Table'[LowerBound]
        )
)
VAR TotalHit = COUNT('Database'[Returns])
RETURN
Hits/TotalHit

However, this gives me the result of the whole table, before applying the slicers. This means it is not dynamic. How can I arrive to do this count and that it remains responsive to any change in the slicers I make.

Thanks,

[amitchandak]

@nicolasvargas , I doubt you can create a table that can change with slicer value.

[nicolasvargas]

@amitchandak so to resolve this, would you recommend to create 8 different measures each with it's own parameters?

[V-lianl-msft · Accepted solution]

Hi @nicolasvargas ,

Use lower bound as slicer and create measure like this:

HitRate2 =
VAR min_lower_bound =
    SELECTEDVALUE ( 'Hit Rate Table'[Lower Bound] )
VAR max_upper_bound =
    SELECTEDVALUE ( 'Hit Rate Table'[Upper bound] )
VAR Hits =
    CALCULATE (
        COUNTROWS (Database),
        FILTER (
            Database,
            Database[Returns] >= min_lower_bound
                && Database[Returns] < max_upper_bound
        )
    )
VAR TotalHit =
    COUNTROWS ( ALL ( Database ) )
RETURN
    IF (
        ISFILTERED ( 'Hit Rate Table'[Lower Bound] ),
        DIVIDE ( Hits, TotalHit ),
        1
    )

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nicolasvargas]

@V-lianl-msft @amitchandak In this case the result works but only for the lower bound. The upper bound it is completely ignored in the formula. It is calculating the cummulative frequency instead of the frequency per tranche. How can we include the upper bound within the formula?

[V-lianl-msft]

Hi @nicolasvargas ,

Is this problem sloved?
If not, please let me know.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft]

Hi @nicolasvargas,

Based on your description, do you want to use both upper and lower bounds as slicers?

I'm a little confused about your scenario.

If the problem persists,could you share the sample pbix via cloud service like onedrive for business?

Please mask any sensitive data before uploading.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@nicolasvargas , Try the solution by @V-lianl-msft