Forum Discussion

nicolasvargas's avatar
nicolasvargas
Helper I
6 years ago
Solved

Create a table with a variable column dependant on slicers

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 BoundUpper boundCount
-1-0.5x
-0.5-0.25x
-0.25-0.1x
-0.10x
00.1x
0.10.25x
0.250.5x
0.51x

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,
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    6 years ago

    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.

7 Replies

      • V-lianl-msft's avatar
        V-lianl-msft
        Community Support
        6 years ago

        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.