Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mikoal
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]

return

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
count
  85311

Percent data

(customer data count/total hourly count)

  10062.537.512.512.5

 

 

 

Total hourly count

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

8

 

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

Slicer Percent 
80 

 

 

 

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
count
    8
Percent data   100

 

 

3 REPLIES 3
mikoal
Helper I
Helper I

Anyone know the solution to this problem?

AUDISU
Resolver III
Resolver III

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.