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
I have a meeting in a few minutes and can't full digest this, but want to make 1 point.
Say you have a calculated column =SUM(MyTable[MyColumn]).
That will return the SUM of the entire table, on every row of the table. It's not going to look at "just the current row". That is because SUM( ) is using a filter context... that doesn't exist when doing a calculated column.
However, if you do =CALCULATE(MyTable[MyColumn]) ... you will then get just the value of MyColumn (not the sum of the whole table). The call to CALCULATE takes the current "row context" (as you walk the table 1 row at a time calculating your calculated column) and converts it into a "filter context" that SUM( ) can understand and use.
Note ALSO that if you did:
My Sum := SUM(MyTable[MyColumn]
and then used that new measure on your calculated column = [My Sum]
you will get the 2nd behavior -- eg, just the sum of the current row. That is because measures have an "implicit" calculate.
I have no idea if this is related to your problems (could also be that you need to use RELATED somewhere), but my gut is... this info will help get you in the right direction :)
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.
I feel as if you are (as I like to say) "off in the weeds". :)
I'm hampered by 2 things:
* Your use a horrible terms like "measure" (for a column) and Sheet1/Table1, etc.
* That there is just really really limited number of times that I try to create calculations and push them into a new table.
From your original mail: "I need to produce a table with count of each string value for a given date range" -- how do you specify this "date range"? From the dax you have shown seems like it is simply "everything before today" ?
Think I also need to know the relationships between 'SheetTable' and 'Table'
Also note that you can add aggregate expressions on your Summarize(SheetTable,SheetTable[Column1]), like:
Summarize(SheetTable,SheetTable[Column1], "The Row Count", COUNTROWS(SheetTable))
(Though, you may need to wrap that countrows in a calculate for the same reason as my last post).
You do not need a new table if you replace 'measure' with 'VAR'.
However; Creating a calculation table ensures each step of the code works before you apply;
it also helps organize, and you can cut down on alot of looping code by just calling simple measures like ''SharedCalcTable: [Column1sumOfstuff] + [Column2averageOfStuff].
In Addition; if you have a shared dataset; you do not need to re-write the code, just 'getData' and pull down your Common calculations table. Great for Finance and Quality stuff.
This style of "New Table'' is an attempt to follow Azure Synapse Workbooks, Jupyter Notebooks, GoogleColab; They all use the same 'modular' approach to Table Code. And its GOOD.