Count if has a link with filter

[uif19085]

 

 

Hello, please help me find a way to make two measures with the following logic:
Firstly, a  link is dictated by Table 2 (Link column) back to Table 1 (UnID column) as in the shown image.
Measure 1: Count the number of "STR" that have at least one link with an "EER" or "SYS" = 3 (

Explanation:  1 (ArtID 1 has link with an EER or SYS) + 0 (ArtID3 has no link with an EER or SYS) + 1 (ArtID 6 has  one link  with EER or SYS) + 1 (ArtID 8 has link with an EER or SYS) 
Measure 2: 
Number of "STR" with implementation in {"implemented"} OR ALL linked "EER" and "SYS" with implementation in {"implemented"} =2 
Explanation: 1 (ArtID 1 is "implemented") + 0 ( ArtID 3 "str" not_implemented and all links are in not_implemented) + 0 (Art 6 "str" not_implemented and not ALL links are implemented, only O-12 is implemented) + 1 (Art 8 "str" not_implemented but all links are in implemented, O-15 and O-12)  

[uif19085]

Hello, @v-yiruan-msft thank you very much, it worked for the first measure, do you have any idea how can i do it for the second one?  

[v-yiruan-msft · Accepted solution]

Hi @uif19085 ,

You can create a measure as below to get it, please find the details in the attachment.

Measure2 = 
VAR _t1count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table 1'[ArtID] ),
        FILTER (
            'Table 1',
            'Table 1'[STR] = 1
                && 'Table 1'[Implementation] = "implemented"
        )
    )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table 1'[ArtID] ),
        FILTER ( 'Table 1', 'Table 1'[STR] = 1 )
    )
VAR _tab1 =
    CALCULATETABLE (
        VALUES ( 'Table 1'[ArtID] ),
        FILTER (
            'Table 1',
            'Table 1'[STR] = 1
                && 'Table 1'[Implementation] <> "implemented"
        )
    )
VAR _uids =
    CALCULATETABLE (
        VALUES ( 'Table 2'[Link] ),
        FILTER ( 'Table 2', 'Table 2'[ArtId] IN _tab1 )
    )
VAR _uids1 =
    CALCULATETABLE (
        VALUES ( 'Table 1'[UnID] ),
        FILTER (
            'Table 1',
            'Table 1'[UnID]
                IN _uids
                && 'Table 1'[Implementation] = "implemented"
                && ( 'Table 1'[SYS] = 1
                || 'Table 1'[EER] = 1 )
        )
    )
VAR _tab2 =
    SUMMARIZE (
        FILTER ( 'Table 2', 'Table 2'[ArtId] IN _tab1 ),
        'Table 2'[ArtId],
        "@count1", COUNT ( 'Table 2'[Link] ),
        "@count2",
            CALCULATE (
                COUNT ( 'Table 2'[Link] ),
                FILTER ( 'Table 2', 'Table 2'[Link] IN _uids1 )
            )
    )
VAR _linkcount =
    COUNTX ( FILTER ( _tab2, [@count1] = [@count2] ), [ArtId] )
RETURN
    _t1count + _linkcount

Best Regards

[v-yiruan-msft]

Hi @uif19085 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Measure = 
VAR _tab1 =
    CALCULATETABLE (
        VALUES ( 'Table 1'[ArtID] ),
        FILTER ( 'Table 1', 'Table 1'[STR] = 1 )
    )
VAR _tabuid =
    CALCULATETABLE (
        VALUES ( 'Table 1'[UnID] ),
        FILTER ( 'Table 1', 'Table 1'[SYS] = 1 || 'Table 1'[EER] = 1 )
    )
VAR _tab2 =
    CALCULATETABLE (
        VALUES ( 'Table 2'[ArtId] ),
        FILTER ( 'Table 2', 'Table 2'[Link] IN _tabuid )
    )
VAR _tab3 =
    INTERSECT ( _tab2, _tab1 )
RETURN
    COUNTX ( _tab3, [ArtId] )

If the above one can't help you get the expected result, 

please provide some raw data in the table 'Main Table' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards