Forum Discussion

vojtechsima's avatar
vojtechsima
Super User
4 years ago
Solved

Virtual Table inside Measure - strange behaviour

Hi, guys,
I got stuck on one issue and I can't figure out why it works like that. I mean I have an idea, but it is still strange.

I have this sample data:

FlightDate|Number|DepartureTime

05.05.2022 123 05:05:59
04.05.2022 123 07:05:59
06.03.2022 56 07:05:59
09.02.2022 58 07:05:59
10.02.2022 456 07:05:59
01.01.2022 1232 07:03:59
01.01.2022 1232 07:05:59


I am trying to figure out if I have [Number] more than one time in my table and If so, evaluate whether it has the same [FlightDate] if not then return False.

I have an idea how to do it but I got stuck on some strange behaviour of the virtual table.
This is my table visual with the current Measure:

TestForDateTable = 
    var currentNumber = MAX(Flights[Number])
    var sameFlightDate = ADDCOLUMNS(SUMMARIZE(Flights, Flights[Number], Flights[FlightDate], "CountOfDuplicates", COUNTROWS(Flights)),"UniqueNumber", Flights[Number])
    var summrazizeNumbes = ADDCOLUMNS(SUMMARIZE(Flights, Flights[Number], "CountOfNumbers", COUNTROWS(Flights)), "UniqueNumber", Flights[Number])

    var MatchNumbers = MAXX(FILTER(sameFlightDate, currentNumber = [UniqueNumber]), [CountOfDuplicates])
    var MatchFlightDate = MAXX(FILTER(sameFlightDate, MatchNumbers = [UniqueNumber]), [CountOfDuplicates])


    return MatchNumbers

This is what the Virtual Table looks like physically:


So basically what I want to do is to take the Current Number filter inside the Virtual Table and assign the proper Count and then display it, however it somehow Displays always 1 instead of 2 when it should be 2. It works when I remove all other columns from my Table Visual but it has to work regardless of the Column I put into the Visual, right?


Could someone please advise what I do wrong and which filter context should I use to eliminate the context it'S getting? I already tried to use ALL and the whole ALL family but it doesn't change the outcome.

Thank you

  • You can use

    Num Different Flights = 
    var currentNumber = SELECTEDVALUE(Flights[Number])
    var summaryTable = CALCULATETABLE( SUMMARIZE( Flights, Flights[FlightDate]), REMOVEFIlTERS(Flights), Flights[Number] = currentNumber)
    return COUNTROWS( summaryTable )
  • I don't see any necessity of complexity according to your description in case of such a simple question.

4 Replies

  • You can use

    Num Different Flights = 
    var currentNumber = SELECTEDVALUE(Flights[Number])
    var summaryTable = CALCULATETABLE( SUMMARIZE( Flights, Flights[FlightDate]), REMOVEFIlTERS(Flights), Flights[Number] = currentNumber)
    return COUNTROWS( summaryTable )
  • CNENFRNL's avatar
    CNENFRNL
    Community Champion

    I don't see any necessity of complexity according to your description in case of such a simple question.

    • vojtechsima's avatar
      vojtechsima
      Super User

      Hi, CNENFRNL 
      Thank you, that's quite the optimization. 😄

      Didn't think about it like that.