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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
D_PBI
Post Patron
Post Patron

DAX measure placed in bar-chart not filtering table

Hi,
I would like to check/confirm something.
Scenario 1)
I have a table visual with several fields, including the Date and ID fields.
I also have a standard bar-chart visual. If I were to drag Date field into the X-axis property and a measure (which is simply the count of the ID field - below) into the Y-axis property, then if I were to click on the bar for one of the months, the table visual will be filtered to show only those records appearing in that month. So even though the measure simply uses a Count on the ID field, it must still know which IDs are present in that measure for it to accurately filter the table visual.

__distinctNumberOfCaseID_All =
VAR __result = DISTINCTCOUNT( 'Cases'[caseid] )

RETURN
    __result + 0



Scenario 2)
I have a table visual with several fields, including the Date and ID fields.
I also have a standard bar-chart visual. If I were to drag Date field into the X-axis property and a measure is placed into the Y-axis. This measure is derived from several measures, resulting in the final measure being placed in the X-axis. This measure is below. However, now when I click on a month's bar it doesn't filter the table visual - why is this?

__Cases_The Case Lead / Inventor is new as a Case Lead / Inventor but has been a Related Participant previously =

VAR _result =

CALCULATE(

    DISTINCTCOUNT( 'Cases'[Case Lead / Inventor (leadinventorid)] ) + 0,

    'Cases'[Case Lead / Inventor (_leadinventorid)] = 'Cases'[contact.contactid]

    && 'Cases'[_Cases_Calculate the earliest Disclosure Date per Case Lead / Inventor] = "Yes"

    && 'Cases'[_Cases_Calculate the earliest Disclosure Date per Related Participant] = "No"

)

 

RETURN

 IF( ISBLANK( _result ), 0, _result )

The measure used in the X-axis in Scenario 2 is another count but for some reason it doesn't filter the table visual.
I appreciate based on my crude description it may not make sense but if you are able to explain why some bar clicking will filter the table visual but when using another measure for the same X-axis it doesn't filter, it would be useful to know.
Thanks.


1 REPLY 1
TomMartens
Super User
Super User

Hey @D_PBI ,

i have to admit that I have some difficulties following your explanation, for this reason, I ask you to create a pbix that contains sample data but still reflect your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or Dropbox and share the link.

 

Nevertheless, it is not the measure that filters the table visual it is the Month. if the Month is part of a calendar table that contains the Date field. When you select a bar, you select the Month and "cross filtering" kicks in.
For this reason, check if the Month column in the bar chart and the Date column in the table visual are are from the same table, ideally from the Calendar table.

 

Hopefully, this provides some ideas of how to tackle this challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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