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
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
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.