cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alisina
Helper III
Helper III

Filter multiple column from 2 different tables

Hi.

 

The issue I am facing is filtering a table based on another tables values i.e. date, wholenumber etc. see example:

image.png

 

The table A is unchanging table (the reference).

The calculation is about how to make Table B show only "Litra" shown in A with same with same "Fokus Gruppen" items as Fejlgruppe in B. Additionally, the Date must be "same or newer" than the one shown  in Table A.

 

I tried some calculate() functions, but it ended circular calculation error.

 

anyone had this problem and solved?

thanks

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @Alisina ,

I'd like to suggest you write measure to compare records between two table and apply it on 'visual level filter' to filter unmatched records.

Sample:

Measure =
VAR temp =
    SELECTCOLUMNS (
        ALLSELECTED ( TableA ),
        "Litra Fokus", [Litra] & " " & [Fokus gruppen]
    )
VAR currLitra =
    SELECTEDVALUE ( TableB[Litra] )
VAR currFokus =
    SELECTEDVALUE ( TableB[Fokus gruppen] )
RETURN
    IF ( currLitra & " " & currFokus IN temp, "Y", "N" )

You can use above measure formula on 'TableB' 'visual level filter' to filter 'Y' result.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Alisina ,

I'd like to suggest you write measure to compare records between two table and apply it on 'visual level filter' to filter unmatched records.

Sample:

Measure =
VAR temp =
    SELECTCOLUMNS (
        ALLSELECTED ( TableA ),
        "Litra Fokus", [Litra] & " " & [Fokus gruppen]
    )
VAR currLitra =
    SELECTEDVALUE ( TableB[Litra] )
VAR currFokus =
    SELECTEDVALUE ( TableB[Fokus gruppen] )
RETURN
    IF ( currLitra & " " & currFokus IN temp, "Y", "N" )

You can use above measure formula on 'TableB' 'visual level filter' to filter 'Y' result.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Hi,

 

Seems I have problem with adding date filter aswell.

So for dates above or equal the listed date in Fokustog must be shown, since it is a "focus".

However, I cannot make it work.

 

Gentaget = 
VAR temp =
    SELECTCOLUMNS (
        ALLSELECTED ( Fokustog );
        "Litra"; [Litra] & " " & [Fokus gruppen];"Dato"; [Dato]
    )
VAR currLitra =
    SELECTEDVALUE ( 'TCI2-ER'[Litra] )
VAR currFokus =
    SELECTEDVALUE ( 'TCI2-ER'[Fejlgruppe] )
VAR currdato =
    SELECTEDVALUE ( 'TCI2-ER'[Dato] )   
RETURN
    IF ( currLitra & " " & currFokus & " " & CALCULATE(currdato;currdato >= Fokustog[Dato])  IN temp; "JA"; "NEJ" )

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors