Filter Measure from Two Tables

[MStP]

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

[v-jingzhang]

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

[Ashish_Mathur]

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