Forum Discussion

uif19085's avatar
uif19085
Icon for Helper III rankHelper III
3 years ago
Solved

Count if has a link with filter

 

 

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)  

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

  • uif19085's avatar
    uif19085
    Icon for Helper III rankHelper III
    3 years ago

    Hello, Anonymous thank you very much, it worked for the first measure, do you have any idea how can i do it for the second one?  

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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