[SOLVED] Dynamic Counting of Rows Based on Primary and Secondary Hub Logic with Image Partitioning

Dynamic Counting of Rows Based on Primary and Secondary Hub Logic with Image Partitioning

I have the following table in Power BI

ID   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 couldn't find a solution to that I am beginner to DAX

Solution

You need to get the count of rows where PrimaryHub matches the slicer selection. Then you exclude rows where PrimaryHub matches the slicer selection and count remaining rows that contain Dubai in SecondaryHub :

CountCIMeasure = 
VAR SelectedPrimaryHub = SELECTEDVALUE('Table'[PrimaryHub])
VAR SelectedImage = SELECTEDVALUE('Table'[Image])

VAR PrimaryHubCount = 
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[PrimaryHub] = SelectedPrimaryHub,
        'Table'[Image] = SelectedImage
    )

VAR SecondaryHubCount = 
    CALCULATE(
        COUNTROWS('Table'),
        'Table'[PrimaryHub] <> SelectedPrimaryHub,
        SEARCH(SelectedPrimaryHub, 'Table'[SecondaryHub], 1, 0) > 0,
        'Table'[Image] = SelectedImage
    )

RETURN 
    PrimaryHubCount + SecondaryHubCount