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...
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.

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.