Count measure except for the selected value in the slicer

[AmiraBedh]

I have the following Fact RAF is 1 to 1 whith Main Hubs table
and Fact Raf is Many to Many with Secondary Hubs
and Fact Raf is Many to 1 with Dim Date

and the Image in the table Fact Raf which is the date of the load data

my measure logic is like below :

I want when for example I select Dubai and Paris from the Main Hubs
it gives for the image 10102024 the count of RAF Id from Fact RAF 50

then in the background without any selection, from the Secondary Hubs I consider all the values except for Dubai and Paris for the same image 10102024 it gives 25

the measure will show 50 + 25 = 75

[Bibiano_Geraldo · Accepted solution]

Hi @AmiraBedh ,

If i understood well, to achieve this you can create a measure that calculates the count of RAF Id based on your selection from the Main Hubs and then adds the count from the Secondary Hubs excluding the selected values by using bellow DAX:

Create a measure for the count of RAF Id based on the Main Hubs selection:

SelectedMainHubsCount = 
CALCULATE(
    COUNT('Fact RAF'[RAF Id]),
    'Main Hubs'[Hub] IN {"Dubai", "Paris"},
    'Fact RAF'[Image] = "10102024"
)

 Now create a measure for the count of RAF Id from Secondary Hubs excluding the selected Main Hubs

ExcludedSecondaryHubsCount = 
CALCULATE(
    COUNT('Fact RAF'[RAF Id]),
    NOT 'Secondary Hubs'[Hub] IN {"Dubai", "Paris"},
    'Fact RAF'[Image] = "10102024"
)

And finally you can combine these measures to get the total count by this DAX:

TotalRAFCount = 
[SelectedMainHubsCount] + [ExcludedSecondaryHubsCount]

Feel free to adjust the measure names and logic as per your specific data model and requirements.

If this repply help you, please consider to accept as solution and give a Kudo.

Thank you

[Anonymous]

Hi @AmiraBedh ,

There are too many tables involved in your question. I can't guess and create sample data to solve your problem. Please provide sample data for all tables and provide some of your expected results based on these sample data. Thank you!

Best Regards,
Dino Tao