Forum Discussion
Using two filters with CALCULATE
Hi Concat,
Just as what scottsen said, you have to use Measures here not Calculated columns.
By the way, if you want to get the topn records of a table by some conditions such as a specific date as you mentioned, you don't have to use rankx, you can create a table using code below to get top 5 records with filtered date:
Table 2 = TOPN(5, Filter(SheetTable, SheetTable[date] > Date(2016, 7, 1)))
Best Regards
Alex
Thanks for the insight scottsen. I am using CALCULATE(), so I would like to think I am on track...
I can get a visualization to filter according to my expectations, as Alex has shown with the measures he has setup... but I cannot replicate it in a Table without creating intermiadary tables with one filter applied at a time.
Working with a table is advantage because I have more at my disposal to manipulate it further. For example, in my original spreadsheet I have 30 distinct values. I only want to show the top 10 counts of these values for a specific date. To do this, I am using a RANKX() measure and applying it to the filter of the visulaization so that only the top 10 are displayed. RANKX() needs a table with the counts already established, so I can't work with measures alone.
Of course, I could determine and filter by the top 10 values myself, but this would require manual intervention everytime new data is imported.
Hi Concat,
Just as what scottsen said, you have to use Measures here not Calculated columns.
By the way, if you want to get the topn records of a table by some conditions such as a specific date as you mentioned, you don't have to use rankx, you can create a table using code below to get top 5 records with filtered date:
Table 2 = TOPN(5, Filter(SheetTable, SheetTable[date] > Date(2016, 7, 1)))
Best Regards
Alex
Seems I have a bit of reading to do regarding when to use measures and when to use calculated columns... Well, thank you both for steering me in the right direction.
As it stands right now, my report is fully functional, but I obviously have not employed the most elegant solution.