Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a dataset that shows the number of visits a URL receives, split into various traffic sources. Is it possible to filter the data in Power BI so that it only shows the data for when a URL has a total number of visits of 2000 or more?
If I were to filter simply by number of visits >= 2000, it will omit the rows for traffic sources that generate less than 2000 visits but the total number for that URL is more than 2000.
Please advise.
Example,
URL | Traffic Source | Visits |
URL1 | Traffic Source1 | 2500 |
URL1 | Traffic Source3 | 600 |
URL1 | Traffic Source3 | 1300 |
URL2 | Traffic Source1 | 1200 |
URL2 | Traffic Source3 | 300 |
URL3 | Traffic Source1 | 3600 |
URL3 | Traffic Source3 | 2800 |
URL3 | Traffic Source3 | 700 |
Total | |
URL1 | 4400 |
URL2 | 1500 |
URL3 | 7100 |
Data for URL2 will be omitted because the total number of vists is less than 2000.
Thanks,
afk
Solved! Go to Solution.
Hi
Create a measure using this formula:
Sub_cnt = CALCULATE(SUM(Table1[Visits]),ALLEXCEPT(Table1,Table1[URL]))
And then apply a visual filter on this measure, where its >2000.
Thanks
Raj
Hi @Anonymous,
Actually, what I did is create a column to act as a flag using your formula to determine the flag value.
Flag column = IF([Sub_cnt] >= 2000, 1, 0)
Then I did a page filter using this flag column to only show data where the flag is equal to 1, as this is only for URLs with a total number of visits of 2000 or higher, and seemed to do the trick!
Thanks for your help
Hi
Create a measure using this formula:
Sub_cnt = CALCULATE(SUM(Table1[Visits]),ALLEXCEPT(Table1,Table1[URL]))
And then apply a visual filter on this measure, where its >2000.
Thanks
Raj
Hi @Anonymous,
I also received the following error message when I tried to copy the DAX formula
"The end of the input was reached"
Yes, you can use this in page filter as well.
Please share the snapshot of formula & error your are getting.
Thanks
Raj
HI @Anonymous,
I'm no longer getting the error message, but I can't drag the measure into the page level or report level filters.
Hi @Anonymous,
Thanks for your suggestion. Is it possible to apply a page or report filter using this?
If not, is there a way around it?
Thanks,
Aqeel
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |