Forum Discussion

nicolasvargas's avatar
nicolasvargas
Icon for Helper I rankHelper 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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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

nicolasvargas's avatar
nicolasvargas
Icon for Helper I rankHelper I
6 years ago

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

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity 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.

    • nicolasvargas's avatar
      nicolasvargas
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        V-lianl-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.