Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Using two filters with CALCULATE

I have large spreadsheet with a date in one column, and a string value in another column and I need to produce a table with count of each string value for a given date range.   So far I have Table ...
Need Help
  • AlexChen's avatar
    AlexChen
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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).