Forum Discussion
How to filter visuals using measures
I have a matrix that has its rows filtered by a measure. On the same report, there is a map visual and a table. The map visual I am able to filter the results as one of the fields (bubble size) is using the measure. However, the map is displaying a different number of records than the matrix visual and I don't know why.
In the table visual, I can not figure out how to have it only show the matching records from the matrix visual. The interactions a there and work when I highlight a record in the matrix, but the initial results show every record.
Do I use a virtual table or parameters or something? I don't actually need the matrix visual, it only serves as a filter.
Thanks. Screenshot and file link below.
https://drive.google.com/file/d/1x2ZUZpl7YF0gXtce5rA0jym2poFWzChX/view?usp=sharing
The fix is to have the rankx formula rank descending. So now earlier dates are ranked 1 and the nearest/later dates are ranked lowest. Here is the date rank calculated column:
Date Rank = RANKX( Filter( Reports, Reports[Service Address] = EARLIER(Reports[Service Address]) ), Reports[Meter Class],,1)and so the solution shows both formulas, here is the moving sum formula:
3 WK IRR SUM = VAR myRank = Reports[Date Rank] return Calculate( SUM( Reports[Watering Days]), Reports[Service Address]=EARLIER(Reports[Service Address]), Filter(Reports, Reports[Date Rank] > myRank-3 && Reports[Date Rank] <= myRank))
5 Replies
americanomuerto , To filter a measure,
You can use a visual level filter
You can create a measure like
Sumx(filter( Summarize(Table, Table[row], table[Col], "_mes",[Measure]), [_mes] >= 10) , [_mes])
Or you can use dynamic Segmentation, to create a dimension
Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kHey Amit,
Thanks for the response. I am a little lost when you get to the "_mes" part. I assume that it is like a variable, but within an expression.
Here is what I tried and it didn't seem to work:15 IRR Days = Sumx(filter( Summarize(Reports, Reports[Service Address],Reports[Irrigation Days In Last 7 Days], "_mes",SUM(Reports[Watering Days])), [_mes] >= 15) , [_mes])Note that the data source in my report is a folder of multiple excel workbooks that each report a weeks worth of data. I have applied a page filter to only look at selected weeks. I will change the weeks that I am looking at, so I wonder if I need to do anything additional to make these calculations work.
An additional avenue I have been thinking about is to create a rolling sum in a calculated column using some type of TopN function.
I feel oh so close.
I created a ranked column to rank the dates for each service address in a calculated column.Date Rank = RANKX( Filter(Reports,Reports[Service Address] = EARLIER(Reports[Service Address])), Reports[Meter Class])Then I created a calculated column to try and SUM the water days value for the current week and the previous two weeks. It is not calculating correctly, so there has to be something wrong with my statement. I tried SUMX first, but was having issues with the grouping and filtering. Here is my current DAX expression:
3 WK IRR SUM = VAR myRank = Reports[Date Rank] return Calculate( SUM( Reports[Watering Days]), Reports[Service Address]=EARLIER(Reports[Service Address]), Filter(Reports, Reports[Date Rank] > myRank-3 && Reports[Date Rank] <= myRank))Here is a picture of the results. I have filtered my data table to just one of the service addresses:
As you can see it is a little off. Rank 6 should just show "2", Rank 4 should show "8", and Rank 1 should show "6."
Looking at the results more, it is clear it is calculating the rolling sum backwards. So rank 6 is calculating ranks 6, 5, and 4 rather than 6, 7, and 8 (which 7 and 8 don't exist). I tried to just flip the < and >, but those left me with null results.
The fix is to have the rankx formula rank descending. So now earlier dates are ranked 1 and the nearest/later dates are ranked lowest. Here is the date rank calculated column:
Date Rank = RANKX( Filter( Reports, Reports[Service Address] = EARLIER(Reports[Service Address]) ), Reports[Meter Class],,1)and so the solution shows both formulas, here is the moving sum formula:
3 WK IRR SUM = VAR myRank = Reports[Date Rank] return Calculate( SUM( Reports[Watering Days]), Reports[Service Address]=EARLIER(Reports[Service Address]), Filter(Reports, Reports[Date Rank] > myRank-3 && Reports[Date Rank] <= myRank))
