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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Evanooruvan
Helper II
Helper II

URL Filtering by dates not working

This is my measure to filter dates to page ( but it is not filtering the dates the page )

url = MIN(Table1[URL])&"?filter=Raw_Data/POSTING_DT ge "&MIN(Table1[POSTING_DT])&" and Raw_Data/POSTING_DT le "&MAX(Table1[POSTING_DT])

 

Evanooruvan_0-1669435094227.png

 

this is how my date looks like 

Evanooruvan_1-1669435147268.png

 

@PaulDBrown 🙂

1 ACCEPTED SOLUTION

There are many ways to display the detail behind a distinctcount measure.

Take this model:

ftable.jpgmodel.jpg

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], ", " )

table display items.jpg

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" )
    )
)

TTp1.jpgTTp2.jpgTTp2.2.jpgTTp3.jpgttp.gif

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:

DT1.jpgSetting up the page will cretae a "Back" button by default to take the user back to the original pageSetting 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 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 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:

COmbine gif.gif

Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
PaulDBrown
Community Champion
Community Champion

What are you trying to get? DISTINCTCOUNT in a row conext will always deliver 1 as the result





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






i have measure column in matrix written using this query 

MAIN = CALCULATE(
    DISTINCTCOUNT(Table1[MISSING_COUNT]),Table[RESULT] = "FAIL")
now i want to add this main column in raw data link such that it would filter  all those values 

the main column measure looke like these 
10
8
3
7

Sorry, I still don't know what you are trying to achieve. What is the "raw data link"? What is the link pointing to? Where do you want to put the link? Which values is it supposed to filter? Where is the column you refer to?

The easiest way we can help you is if you provide sample data and a depiction of what you are trying to achieve





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I have this table visual in pbi desktop, this is how it looks , the yellow one is column header names and above that it shows what kind of column it is.

Evanooruvan_0-1669466939950.png

 

now i have the column MF IN THE TABLE WHICH IS CREATED BY USING THIS MEASURE

MF = CALCULATE(
    DISTINCTCOUNT(Table1[MISSING_COUNT]),Table[RESULT] = "FAIL")

My REQUIREMENT IS : i want to create a url filter parameter such that , when i click on the link 
lets say MF=3 , IT SHOULD DISPLAY ALL 3 RECORDS OF THE CLICKED MF 

THANK YOU PAUL 🙂

 

 

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

There are many ways to display the detail behind a distinctcount measure.

Take this model:

ftable.jpgmodel.jpg

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], ", " )

table display items.jpg

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" )
    )
)

TTp1.jpgTTp2.jpgTTp2.2.jpgTTp3.jpgttp.gif

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:

DT1.jpgSetting up the page will cretae a "Back" button by default to take the user back to the original pageSetting 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 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 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:

COmbine gif.gif

Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

sorry if it is confusing, i am confused as well

PaulDBrown
Community Champion
Community Champion

Sorry, I don't understand what you are after. Can you please post sample data and a depiction of the expected outcome?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Thanks paul , but i found the issue can you help me with this 
Below is a measure , how to convert this into a calculated column, might be silly
thanks

 

MAIN = CALCULATE(
    DISTINCTCOUNT(Table1[MISSING_COUNT]),Table[RESULT] = "FAIL")

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.