Counting values of 2 columns in the same table, using a single slicer?

[peterpm]

Hi all,

I'm looking to get a COUNTA of 2 columns in the same table, that share the same values, through a slicer option. My table has some 30 columns, 2 of them being the following:

Departures	Arrivals
New York	Lisbon
Liverpool	Glasgow
Madrid	Lisbon
Frankfurt	Madrid
Boston	Barcelona
Lisbon	Morocco
Dublin	New York

... and so on, for nearly 1M rows.

The issue is: I created a lookup table with all the available names by joining both Departure and Arrivals, removing duplicates, and named the table "Cities List", and would like to use that table for a simple slicer. But I obviously cannot create 2 relationships for those 2 columns, since Departure and Arrivals are in the same table.

The ouput should be, for example, if I select "Lisbon" in the slicer I'd get 3 as a result, selecting simultaneously "Lisbon" and "New York", I'd get 5.

I've tried:

CALCULATE(COUNTA('Flight registry'[Departures]),'Flight registry'[Departures]=SELECTEDVALUE('Cities list'[Names]))
+
CALCULATE(COUNTA('Flight registry'[Departures]),'Flight registry'[Arrivals]=SELECTEDVALUE('Cities list'[Names]))

which does work but only if I select a single city. If multiple cities are selected, it returns a blank value instead of the sum of both COUNTA. If no value is selected, the default should be the full sum of all instances in both columns (14 in his example), but instead, it also returns a blank value.

Any way to accomplish this via DAX or something else entirely outside the box that I could learn from this problem, would be incredibly appreciated.

Thanks for any help!

[danextian · Accepted solution]

Hi @peterpm 

SELECTEDVALUE returns a single value only if a single value is selected. More than that, it will return blank. So if you select more than one, you're tellinig your measure to count the cities equal to blank.

Change your measure to

Count = 
CALCULATE (
    COUNTA ( 'Flight registry'[Departures] ),
    'Flight registry'[Departures] IN VALUES ( 'Cities list'[Names] )
)
    + CALCULATE (
        COUNTA ( 'Flight registry'[Departures] ),
        'Flight registry'[Arrivals] IN VALUES ( 'Cities list'[Names] )
    )

Please note that if you don't select a city, the count becomes the total number of rows x 2.

[peterpm]

Thank you so much @danextian , you've taken quite a load off my shoulders. Got started with Power BI a couple of months ago and this particular situation has been constant in my reporting. Cheers!