Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Filtering multiple visuals based on measures

Hi there,

Im a new PBI user and have been having a lot of trouble with this software. 
I want to try to filter a visual (Matrix view of hourly data) based on percentages of the customer's overall percentage (based off of a selected time frame). But when i filter it on that visual, it converts the percentage into hourly values. 
This has to do with context I believe.


Refer to the tables below, I can filter visual B correctly when a user enters a percent. But I cannot filter Visual A properly.

To futher clarify, if I enter 80% only 1 customer would appear. If I enter 20%, then 3 customers would appear.

I would like Visual A to show the corresponding customers like Visual B.



What I've tried something like this....this code below, sort of works, but brings new issues.


test=summarize(table,"rowcount", calculate(countrows(allselected(table)),allexcept(table, Date[fiscalyear], table[customers])))


Filter = 

Var value = percent[percentvalue]/100

var pickedvalue= test/table[totalhourlycount]


if (pickedvalue>=value,1,0)

This above sort of works but i have pay attention to 2 things.

1) This works only for fiscal years, if I were to enter a custom date range that is anything but a fiscal year, the above does NOT work. (This is because I just so happen to select the date range of a fiscal year, but custom date ranges will NOT work)

2) I have to change the rows and columns. I originally have Date on Rows, and Customers on columns, but if I swapped them. Then the filtering works. BUT this leads to other problems.

           a) The formatting looks weird

           b) the columns are only limited to 100 columns. For hourly data, theres 8760 hours in 1 year.


I did find a work around but using inforiver. For some reason I do not know why, I can enter the data into inforiver and can be filtered properly in the correct format.

The issue of the custom date still remains.

And a new issue that I cannot export this without paying a subscription


I would like to know how to do this properly without inforiver, with custom dates, with the proper formatting.


Thanks in advanced!




Visual A

DateCustomer 1Customer 2Customer 3Customer 4Customer 5
1/1/2000 12:00     1    
1/1/2000 13:00    1    
1/1/2000 14:00    1    
1/1/2000 15:00    22   
1/1/2000 16:00    34   
1/1/2000 17:00    442  
1/1/2000 18:00    531  
1/1/2000 19:00    32111



Visual B

 Customer 1Customer 2Customer 3Customer 4Customer 5
Customer data

Percent data

(customer data count/total hourly count)





Total hourly count

(The date range shown in the example has 8 hours, so i would do a datediff (min, max))



User enters the % (in this case, 80 was entered)

Slicer Percent 




Desired results based on the 80%



Visual A

DateCustomer 1
1/1/2000 12:00     1
1/1/2000 13:00    1
1/1/2000 14:00    1
1/1/2000 15:00    2
1/1/2000 16:00    3
1/1/2000 17:00    4
1/1/2000 18:00    5
1/1/2000 19:00    3



Visual B

 Customer 1
Customer data
Percent data   100



Helper I
Helper I

Anyone know the solution to this problem?

Resolver II
Resolver II

Hi @mikoal , 

First you have to change data table layout.

Could you please tell me the header names in your data table?

Than I will tell you which columns should be changed.

Thank you.

The data table layout is as follows, there are 100 million rows in this data table A. Table A is the main data table.
Table B joins the zone and city

Table C is a date table (use it for the date slicer)

Table D is a time table (I dont really use) 




TABLE A                             
 Customer  UnitGMTDSTHourlyUsage  YearMonth  DayHour  DSMT+GMT  Datetime  Date  Time                  
TABLE B                             
 Customer  Rate  Rate2  Rates description  City  Zone                       
TABLE C                             
 Date yearstart of yearend of yearmonthstart of monthend of monthdays in monthdayday name dayof weekday of yearmonth namequarterstart of quarterend of quarterweek of yearweek of monthstart of weekend of weekfiscal yearfiscal quarterfiscal monthday offsetmonth offsetyaer offsetquarter offsettodayflagperiod
Table D                             
 hourminsecondam/pmhour labelminute labelsecond labeltimekeyhour bin 12hour bin 8hour bin 4hour bin 3hour bin 2minute bin 30minute bin 15minute bin 10             

Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors