Filtering column based on another table based on multiple criteria

[Anonymous]

Hi,

I've only recently started working with Power Bi. I simply cannot get any further with the following question.

In Table1, all values of the ID column should output which are either not contained with the ID column in Table2

OR that do not meet the following criteria in table 2: isValid = 1 AND isComplete = 1.

Later on the dashboard the output must then be further limited by a time slicer.

Table1

ID	Name1	Name2	City
DE-001	Mike	S.	B.
DE-002	Ted	F.	L.
DE-003	Frank	M.	F.
DE-004	Marc	C.	T.
DE-005	Fiona	F.	L.

Table2

ID	isValid	isComplete	Date
DE-001	0	0	04.04.2016
DE-002	1	0	07.04.2016
DE-003	0	1	23.05.2016
DE-004	1	1	23.05.2016
DE-004	0	0	04.08.2017
DE-007	0	0	16.08.2017

Without a time slicer the output should be: DE-001, DE-004, DE-005

With a time slicer set to the whole year 2017: DE-004, DE-005

Thank you so much for your suggestions!

Sunny greetings from Babelsberg,

Sigmund

[v-shex-msft]

Hi @Anonymous,

I think you can use condition to extract the id list from 'table 2', then you can use 'in' operator or INTERSECT function to compare with current id in 'table 1' and id list and return Tag and use it on 'visual level filter' to control displayed records.

Measure filter:

Measure =
VAR currID =
    VALUES ( Table1[ID] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table2[ID] ),
        FILTER ( ALL( Table2 ), [isValid] = 1 && [isComplete] = 1 )
    )
RETURN
    IF ( INTERSECT ( cuddID, list ) = 0, "Y", "N" )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

As far as I can see, your approach goes exactly in the right direction. 

I don't get an error message with the following code:

Measure = 
        VAR currID =
            VALUES ( Table1[ID] )
            VAR list =
            CALCULATETABLE (
                VALUES ( Table2[ID] );
                FILTER ( ALL( Table2 ); [isValid] = 1 && [isComplete] = 1 )
            )
        RETURN
            IF ( INTERSECT ( currID; list ) = 0; "Y"; "N" )

But if I want to integrate the measure into a visual: I get the following error message: MdxScript (Model) (10, 41) Calculation error in Measure 'Table1' [Measure]: DAX comparisons do not support comparisons between values ​​of the type text and integer. You can use the VALUE function or the FORMAT function to convert one of the values.

[ImkeF]

Hi @Anonymous ,

please make sure that columns "isValid" and "isComplete" are both formatted the same in the query editor, as well as the ID columns in both tables.

[Anonymous]

Hi @ImkeF,

Thank's for your feedback. ID is formatted as text, isValid and isNull are formatted as integers.
However, I still get the error message that text cannot be compared to integers.

[v-shex-msft]

Hi @Anonymous,

Sorry, it seems like I forget to add 'COUNTROWS' function to package the INTERSECT function result. (this issue should be caused by comparing with list/table type value and integer type value)

DAX INTERSECT 

Measure =
VAR currID =
    VALUES ( Table1[ID] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table2[ID] );
        FILTER ( ALL ( Table2 ); [isValid] = 1 && [isComplete] = 1 )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( currID; list ) ) = 0; "Y"; "N" )

If above still not helps, can you please share a dummy sample pbix file to test?

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft;

Thanks again! How can I upload sample pbix file? That's what I get with the new measure.

[v-shex-msft]

HI @Anonymous,

As I said, this formula is a measure filter. So you need to add this to a table visual that used table1 fields and filter 'Y' tag records.

Applying a measure filter in Power BI 

For pbix sample file, you can upload it to 'onedrive for business' and share the link here. (notice: do not attach any sensitive data)

Regards,
Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

Big thanks, I think we are almost there. The filter works almost as desired. DE-001 and DE-005 are correct displayed.

However, DE-002 and DE-003 are displayed, which should be filtered out and DE-004 is missing, although it meets the criteria.

You can find all the data here: https://www.magentacloud.de/share/bsog4e.dlt 

[v-shex-msft]

Hi @Anonymous,

So you mean you still want to keep the filter effect on your table? if this is a case, you can use allselected to replace all function, it will keep the current filter effects:

Measure =
VAR currID =
    VALUES ( Table1[ID] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table2[ID] ),
        FILTER ( ALLSELECTED ( Table2 ), [isValid] = 1 && [isComplete] = 1 )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( cuddID, list ) ) = 0, "Y", "N" )

Notice: these tables should no link based on relationship or these filter effects will also apply to your visuals.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

It seems, that ALLSELECTED works differently than expected. With the test data shown below, I get in some cases the same result as when I’ m using an INTERSECT from Table11 and Table22.

This led me to the following quick and dirty solution.

1. Intersect from Table11 and Table22 to find out all IDs that are not included in Table22.

T1withOutT2 = 
VAR currID =
    VALUES ( Table11[ID] )
VAR list =
    VALUES ( Table22[ID] )
RETURN
    IF ( COUNTROWS ( INTERSECT ( currID; list ) ) = 0; "1"; "2" )

2. Filtering those in table2 who meets the criteria.

T2isFalse = 
VAR currID =
    VALUES ( Table11[ID] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table22[ID] );
        FILTER ( Table22 ; [isValid] = 0 && [isComplete] = 0 )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( currID; list ) ) = 0; "1"; "2" )

3. To filter false negatives (in this case it's DE-004) I used the measure notFalse und filtered the visual. If you then filter notFalse to not equal 3, you'll get the desired result.

notFalse = [T1withOutT2]+[T2isFalse]

As far as I can see it works. But please help me to find a more elegant solution. Thank you so much!

Best regards,

Sigmund

[v-shex-msft]

Hi @Anonymous,

According to snapshot, it seems like I forget to add a condition to check if table 1 id exists in table 2.I modify measure formula to add the condition to confirm that situation:

Measure =
VAR T1List =
    VALUES ( Table11[ID] )
VAR T2List =
    CALCULATETABLE (
        VALUES ( Table22[ID] );
        FILTER ( Table22; ( [isValid] = 0 && [isComplete] = 0 ) || [ID] IN currID )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( T1List; T2List ) ) = 0; "Y"; "N" )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

Thanks, did you mean?

Measure = 
VAR T1List =
    VALUES ( Table11[ID] )
VAR T2List =
    CALCULATETABLE (
        VALUES ( Table22[ID] );
        FILTER ( Table22; ( [isValid] = 0 && [isComplete] = 0 ) || [ID] IN T1List )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( T1List; T2List ) ) = 0; "Y"; "N" )

The result doesn't differ from the previous measure based on the test data.

[Anonymous]

Hi @v-shex-msft,

It seems, that ALLSELECTED works differently than expected https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/ . With the test data shown below, I get in some cases the same result as when I’ m using an INTERSECT from Table11 and Table22.

This led me to the following quick and dirty solution:

1. Intersect from Table11 and Table22 to find out all IDs that are not included in Table22.

T1withOutT2 = 
VAR currID =
    VALUES ( Table11[ID] )
VAR list =
    VALUES ( Table22[ID] )
RETURN
    IF ( COUNTROWS ( INTERSECT ( currID; list ) ) = 0; "1"; "2" )

2. Filtering those in Table22 who meets the criteria.

T2isFalse = 
VAR currID =
    VALUES ( Table11[ID] )
VAR list =
    CALCULATETABLE (
        VALUES ( Table22[ID] );
        FILTER ( Table22 ; [isValid] = 0 && [isComplete] = 0 )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( currID; list ) ) = 0; "1"; "2" )

3. To filter false negatives (in this case DE-004) I used the measure notFalse und filtered the visual. If I now filter notFalse with unequal 3, I get the desired result.

notFalse = [T1withOutT2]+[T2isFalse]

As far as I can see it works. But please help me to find a more elegant solution. Thank you so much!

Best regards,
Sigmund

[amitchandak]

If Table1 is a master table for table 2 you can join them of ID

You can create a measure like

Calculate(count(Table2[ID]),Table2[isValid]=1,Table2[isComplete]=1)

And use that is you visual , that should work

[Anonymous]

Hi @amitchandak,

Thanks for your support. When I'm using this code, it just counts the IDs meeting the criteria but I don't see the filtered IDs as I want to. Beside that the measure is based on Table2. So what should I do?

CALCULATE(COUNT(Table2[ID]); Table2[isValid]=0; Table2[isComplete]=0)