0

I have a model of 3 tables:

enter image description here

I want to calculate the number of years a pilot has been active (i.e. at least 1 flight per year, regardless of how many flights):

enter image description here

I thought that dragging the PilotName along with the below DAX measure would solve my problem, but it doesn't:

Years Active:=COUNT(Season[SeasonId])

Is it possible to achieve this without any distinct count?

Victor
  • 1,163
  • 4
  • 25
  • 45

1 Answers1

0

A solution could be SUMMARIZE,i recreate your situation on my excel, creating some complications:

enter image description here

As you can see i indroduce Some Flightlanded = 0 and For same pilot and season id 2 different rows. I create your measure (Years active old) and my measure:

Years Active:=calculate(COUNTX(summarize(tableflight,tableflight[Seasonid],tableflight[PilotPK]),tableflight[Seasonid]),filter(tableflight,tableflight[Flightlanded]=1))

This formula not use distinct count and can answer to 2 problem:

  1. Same row with different value
  2. A row that we don't want to consider ( flight = 0)
nicolò grando
  • 397
  • 1
  • 9
  • Thanks for the answer! After understanding your code I changed the COUNTX() for COUNTROWS() and I get the same result. Should COUNTROWS() provide better performance? – Victor Apr 06 '16 at 16:09