Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I don't see any necessity of complexity according to your description in case of such a simple question.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi, @CNENFRNL
Thank you, that's quite the optimization. 😄
Didn't think about it like that.
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 )
Hi, @johnt75
Didn't think about this point of view. Thank you, it obviously worked.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |