Forum Discussion
URL Filtering by dates not working
- 3 years ago
There are many ways to display the detail behind a distinctcount measure.
Take this model:
To show the distinctcount of items by category where the validity is "Yes", we have this measure:
Items where Validity is Yes = CALCULATE ( DISTINCTCOUNT ( 'Item Table'[dItem] ), FILTER ( 'Validity Table', 'Validity Table'[dValidity] = "Yes" ) )If you want to list the items in a table visual, you can use a measure as follows:
Display Items = VAR _Items = CALCULATETABLE ( VALUES ( 'Item Table'[dItem] ), FILTER ( 'Item Table', [Items filter] >= 1 ) ) RETURN CONCATENATEX ( _Items, 'Item Table'[dItem], ", " )If you want the values to be displayed as a tooltip, you can create a report tooltip page, with for example a table visual for items. You will need the following measure to filter the visual to display the correct values:
Items filter = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Item Table'[dItem] ), FILTER ( 'Validity Table', 'Validity Table'[dValidity] = "Yes" ) ) )If you actually want to be able to drillthrough to a new page where there is a visual listing the values, you can set it up as follows:
Setting up the page will cretae a "Back" button by default to take the user back to the original pageYou navigate to the drillthrough page by right-clickin on a rowYou can set up a more direct drillthrough be setting it up in the Data/drill tab (but you will loose the default tooltips)
You can also combine these techniques if you wish: for example Report Page Tooltip & Drillthrough:
Sample PBIX file attached
When you say you want to create a URL field parameter, do you mean to the source data? If so, it won't work. You can create a parameter in Power Query to filter data on import, but this is not dynamic in the sense that once the import occurs, the dataset is set.
thanks paul you are being very helpful 🙂
i thought so too that way, is there any other way that on click on mf = 3 and drill through that it list all 3 values.
- PaulDBrown3 years agoCommunity Champion
There are many ways to display the detail behind a distinctcount measure.
Take this model:
To show the distinctcount of items by category where the validity is "Yes", we have this measure:
Items where Validity is Yes = CALCULATE ( DISTINCTCOUNT ( 'Item Table'[dItem] ), FILTER ( 'Validity Table', 'Validity Table'[dValidity] = "Yes" ) )If you want to list the items in a table visual, you can use a measure as follows:
Display Items = VAR _Items = CALCULATETABLE ( VALUES ( 'Item Table'[dItem] ), FILTER ( 'Item Table', [Items filter] >= 1 ) ) RETURN CONCATENATEX ( _Items, 'Item Table'[dItem], ", " )If you want the values to be displayed as a tooltip, you can create a report tooltip page, with for example a table visual for items. You will need the following measure to filter the visual to display the correct values:
Items filter = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Item Table'[dItem] ), FILTER ( 'Validity Table', 'Validity Table'[dValidity] = "Yes" ) ) )If you actually want to be able to drillthrough to a new page where there is a visual listing the values, you can set it up as follows:
Setting up the page will cretae a "Back" button by default to take the user back to the original pageYou navigate to the drillthrough page by right-clickin on a rowYou can set up a more direct drillthrough be setting it up in the Data/drill tab (but you will loose the default tooltips)
You can also combine these techniques if you wish: for example Report Page Tooltip & Drillthrough:
Sample PBIX file attached
- Evanooruvan3 years agoHelper II
thank you very much paul for putting effort in this, i will try this method and let you know might be delayed by a day or two 🙂
world needs more people like you, thanks again.