Forum Discussion
Helper IFilter Measure from Two Tables
Hello Everyone,
I want to create a measure that will be applied as visual-level filter. This will use one column from Table A and one column from Table B. The conditions are:
Table A:
Column Name: PR Account Assignment
Values to Include: K, P
Table B:
Column Name: PO Account Assignment
Values to Include: K, P, and Blank, but only include blank rows if the PR Account Assignment value from Table A is K or P. There are blank rows in Table B that do not reference a value of K or P in Table A - I want to exclude those rows.
Please provide a DAX expression that will meet these conditions. I understand it might involve CALCULATE and CROSSFILTER but I cannot find a solution that matches my specific example, and frankly, I am very new at this. Thank you!
Michael
4 Replies
- Ashish_Mathur
Super User
Hi,
Share some data, explain the question and show the expected result.
- MStP
Hello Ashish_Mathur
As posted above, in reply to your message:
Please see the link for the sample data file below.
I want to create a measure that acts as a visual level filter to include the following rows:
PO Cat = K or P
PO Cat = Blank, and PR Cat is K or P
In the sample data, the desired result is that the filter excludes PO1003.
https://drive.google.com/file/d/1XOp8YVIYCD71NXyVb3Oe_2lRT7FXUWgl/view?usp=sharing
Please let me know if you need additional information.
Michael
v-jingzhangCommunity Support
Hi MStP
Can you provide some sample data to help explain the expected outcome more clearly? Is there any relationship between two tables?
If you display data from Table B in a table visual, you can simply put PO Account Assignment column from Table B into the filter pane of this visual and set it to show items when value is 'K' or 'P' with Basic filtering type.
If the selected values from Table A are decided by other visuals based on user's interaction, you can create a measure like below to play as a visual-level filter field. Set it to show items when the value is 1. To use this measure, [PR Account Assignment] column should be displayed in the visual.
flag = VAR __selectedValues = VALUES ( 'Table A'[PR Account Assignment] ) RETURN IF ( SELECTEDVALUE ( 'Table B'[PO Account Assignment] ) IN __selectedValues, 1, 0 )Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.- MStP
Hello v-jingzhang
Please see the link for the sample data file below.
I want to create a measure that acts as a visual level filter to include the following rows:
PO Cat = K or P
PO Cat = Blank, and PR Cat is K or P
In the sample data, the desired result is that the filter excludes PO1003.
https://drive.google.com/file/d/1XOp8YVIYCD71NXyVb3Oe_2lRT7FXUWgl/view?usp=sharing
Please let me know if you need additional information.
Michael
