Calculate the links between other columns

[uif19085]

Hello, i need some help with the following logic:
Here 1 counts as True:

ID	UniqueID	InLink	STR	SYR	EER	MER
1	Aa	Bb	1	0	0	0
2	Bb	Aa	0	1	0	0
3	Cc	Dd	0	1	0	0
4	Dd	Ee	0	0	0	1
5	Ee	Dd	1	0	0	0
6	Ff	Gg	1	0	0	0
7	Gg	Ff	0	0	1	0

Total number of STR(1,5,6) inlinked by one or many SYR or EER that would be =  1 (link with 2/Bb) + 1(link with 7/Gg) = 2

Total number of SYR(2,3) inlinked by one or many STR or MER = 1 ( link with 1/Aa) + 1 ( link with 4/Dd) = 2
What's the best approach?

[Anonymous · Accepted solution]

Hi @uif19085 ,

Please try below steps:

1. create two measure with below dax formula

Total STR =
VAR tmp1 =
    CALCULATETABLE (
        VALUES ( 'Table'[InLink] ),
        FILTER ( ALL ( 'Table' ), [STR] = 1 )
    )
VAR tmp2 =
    CALCULATETABLE (
        VALUES ( 'Table'[UniqueID] ),
        FILTER ( ALL ( 'Table' ), [SYR] = 1 )
    )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Table'[UniqueID] ),
        FILTER ( ALL ( 'Table' ), [EER] = 1 )
    )
VAR _a =
    INTERSECT ( tmp1, tmp2 )
VAR _b =
    INTERSECT ( tmp1, tmp3 )
RETURN
    COUNTROWS ( _a ) + COUNTROWS ( _b )

Total SYR =
VAR tmp1 =
    CALCULATETABLE (
        VALUES ( 'Table'[InLink] ),
        FILTER ( ALL ( 'Table' ), [SYR] = 1 )
    )
VAR tmp2 =
    CALCULATETABLE (
        VALUES ( 'Table'[UniqueID] ),
        FILTER ( ALL ( 'Table' ), [STR] = 1 )
    )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Table'[UniqueID] ),
        FILTER ( ALL ( 'Table' ), [MER] = 1 )
    )
VAR _a =
    INTERSECT ( tmp1, tmp2 )
VAR _b =
    INTERSECT ( tmp1, tmp3 )
RETURN
    COUNTROWS ( _a ) + COUNTROWS ( _b )

2. add two card visual with above measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please explain your logic in more detail, and maybe provide some more context. As it is this is not clear (at least to me)