Dynamic counting of rows based on primary and secondary hubs for multiple selection of hubs based on

[MagicMind]

I have the following table in Power BI

CI   PrimaryHub    Secondaryhub         Image
1    Dubai         Dubai/Paris          01102024
2    Dubai         Dubai/Tokyo          01102024
3    Lisbon        Lisbon/Dubai/Paris   01102024
4    Madrid        Madrid/Dubai         01102024
5    Tunis	   Tunis/Algiers        01102024
1    Dubai         Dubai/Paris          01112024
5    Paris         Paris/Dubai/Algiers  01112024
6    Dubai         Dubai/Alexandria     01112024

To count the number of CI for Dubai we look to PrimaryHub = Dubai which is equal to 2 when the Image = 01102024

CI   PrimaryHub    Secondaryhub Image
1    Dubai         Dubai/Paris  01102024
2    Dubai         Dubai/Tokyo  01102024

then we exclude the rows that have PrimaryHub = Dubai :

CI   PrimaryHub    Secondaryhub  Image
1    Dubai         Dubai/Paris   01102024
2    Dubai         Dubai/Tokyo   01102024

and check the remaining rows that have Dubai in the values of Secondaryhub which is in this case 2 rows :

CI   PrimaryHub    Secondaryhub         Image
3    Lisbon        Lisbon/Dubai/Paris   01102024
4    Madrid        Madrid/Dubai         01102024

the final count is 2 +2 = 4

it also depends on the Image when there is another image 01112024 : To count the number of CI for Dubai we look to PrimaryHub = Dubai which is equal to 2

1    Dubai         Dubai/Paris          01112024
6    Dubai         Dubai/Alexandria     01112024

then we exclude the rows that have PrimaryHub = Dubai for that partition or image 01112024

1    Dubai         Dubai/Paris          01112024
6    Dubai         Dubai/Alexandria     01112024

and check the remaining rows that have Dubai in the values of Secondaryhub which is in this case 1 rows :

5    Paris         Paris/Dubai/Algiers  01112024

the final count is 2 +1 = 3

the image is a text representation for DDMMYYYY when the records are loaded

I want when I put primaryhub in a slicer for any selection it will give me the result like the logic i showed you

if i select dubai and paris from the same image the final count will be 4 + 7 = 10

CI_Count := 
VAR SelectedPrimaryHubs = VALUES('YourTable'[PrimaryHub])
VAR SelectedImage = SELECTEDVALUE('YourTable'[Image])

VAR PrimaryHubCount = 
    SUMX(
        SelectedPrimaryHubs,
        CALCULATE(
            COUNTROWS('YourTable'),
            'YourTable'[PrimaryHub] = [PrimaryHub],
            'YourTable'[Image] = SelectedImage
        )
    )


VAR SecondaryHubCount = 
    SUMX(
        SelectedPrimaryHubs,
        CALCULATE(
            COUNTROWS('YourTable'),
            NOT 'YourTable'[PrimaryHub] IN SelectedPrimaryHubs,
            SEARCH([PrimaryHub], 'YourTable'[Secondaryhub], 1, 0) > 0,
            'YourTable'[Image] = SelectedImage
        )
    )

RETURN PrimaryHubCount + SecondaryHubCount

I get this following error :

a table of multiple values was supplied where a single value was expected

[johnt75]

You're getting an error because SEARCH needs a single text value but you're passing in multiple selections. I think the below should work. It splits the secondary hubs down to their individual components so a filter can be applied

CI Count = 
VAR PrimaryCount = CALCULATE(
    COUNTROWS('Table'),
    TREATAS(
        VALUES(Hub[PrimaryHub]),
        'Table'[PrimaryHub]
    )
)
VAR SecondarySplits = GENERATE(
    SELECTCOLUMNS(
        FILTER(
            ALL('Table'),
            NOT 'Table'[PrimaryHub] IN VALUES( 'Hub'[PrimaryHub] )
        ),
        'Table'[Secondaryhub],
        "@HubPath", SUBSTITUTE(
            'Table'[Secondaryhub],
            "/",
            "|"
        )
    ),
    VAR _PathLength = PATHLENGTH([@HubPath])
    VAR _Hubs = SELECTCOLUMNS(
        GENERATESERIES(
            1,
            _PathLength
        ),
        "@Hub", PATHITEM(
            [@HubPath],
            [Value]
        )
    )
    RETURN
        _Hubs
)
VAR _MatchingHubs = SELECTCOLUMNS(
    FILTER(
        SecondarySplits,
        [@Hub] IN VALUES('Table'[PrimaryHub])
    ),
    'Table'[Secondaryhub]
)
VAR SecondaryCount = CALCULATE(
    COUNTROWS('Table'),
    NOT 'Table'[PrimaryHub] IN VALUES( 'Hub'[PrimaryHub] ),
    TREATAS(
        _MatchingHubs,
        'Table'[Secondaryhub]
    )
)
VAR Result = PrimaryCount + SecondaryCount
RETURN
    Result

[MagicMind]

I don't have dimhub , everything is in the same table

[johnt75]

Apologies, I left that part out of the explanation. You can create a disconnected table 

Hub = DISTINCT( 'Table'[PrimaryHub] )

and use that in your visuals.

[AmiraBedh]

From what I understood from the OP he has only one table, I can't see the DimHub.