cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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,
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Create a table with a variable column dependant on slicers

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.

View solution in original post

7 REPLIES 7
Highlighted
Super User IX
Super User IX

Re: Create a table with a variable column dependant on slicers

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Create a table with a variable column dependant on slicers

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

Highlighted
Microsoft
Microsoft

Re: Create a table with a variable column dependant on slicers

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.

View solution in original post

Highlighted
Super User IX
Super User IX

Re: Create a table with a variable column dependant on slicers

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Create a table with a variable column dependant on slicers

@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?
Annotation 2020-07-21 113418.png

Highlighted
Microsoft
Microsoft

Re: Create a table with a variable column dependant on slicers

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.

Highlighted
Microsoft
Microsoft

Re: Create a table with a variable column dependant on slicers

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors