Forum Discussion

chinthada's avatar
chinthada
Icon for Helper I rankHelper I
6 years ago
Solved

Quarterly Count with multiple filters

Hi,

I want to create a measure to show quarterly count of records with multiple filters. The data table is like below,

datatable

For this example, the result (record count) should be = 4


I tried below measure with DistinctCount

CustomMeasure = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER__ID]), 'VW_Water'[Fiscal_Quarter], filter('VW_Water','VW_Water'[CC_MAST_SUBTYPE]= "E*LEAK" && 'VW_Water'[EarlyClosure]= "Double up" && 'VW_Water'[Activity]= "Complete"))

 

The output should be like this.

 

PBI Visual

Appreciate your help.

thanks

Need Help
Show and Tell
Tips and Tricks
  • StevenGrenier's avatar
    StevenGrenier
    6 years ago

    The syntax for CALCULATE is CALCULATE([expression], filter, filter, filter...). You are trying to add multiple filters in the same FILTER statement by using &&. Simply add more FILTER statements and it should work.

     

    CustomMeasure = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER__ID]), FILTER('VW_Water','VW_Water'[CC_MAST_SUBTYPE]= "E*LEAK"), FILTER('VW_Water','VW_Water'[EarlyClosure]= "Double up"), FILTER('VW_Water','VW_Water'[Activity]= "Complete"))

     

    Hope this helps!

5 Replies

  • StevenGrenier's avatar
    StevenGrenier
    Frequent Visitor
    6 years ago

    The syntax for CALCULATE is CALCULATE([expression], filter, filter, filter...). You are trying to add multiple filters in the same FILTER statement by using &&. Simply add more FILTER statements and it should work.

     

    CustomMeasure = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER__ID]), FILTER('VW_Water','VW_Water'[CC_MAST_SUBTYPE]= "E*LEAK"), FILTER('VW_Water','VW_Water'[EarlyClosure]= "Double up"), FILTER('VW_Water','VW_Water'[Activity]= "Complete"))

     

    Hope this helps!

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      StevenGrenier 

      Try

      CustomMeasure = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER__ID]), FILTER('VW_Water','VW_Water'[CC_MAST_SUBTYPE]= "E*LEAK" && 'VW_Water'[EarlyClosure]= "Double up" && 'VW_Water'[Activity]= "Complete"))

CustomMeasure = CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER__ID]),'VW_Water'[CC_MAST_SUBTYPE]= "E*LEAK", 'VW_Water'[EarlyClosure]= "Double up",'VW_Water'[Activity]= "Complete")

      if you need more help make me @

      Appreciate your Kudos.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

    The easiest would be to just apply filter or create slicers and then select.  If you are OK with this approach, then this measure will work

    =DISTINCTCOUNT('VW_Water'[CC_MASTER__ID])

    If you do not wish to use slicers/filters, then write this measure

    =CALCULATE(DISTINCTCOUNT('VW_Water'[CC_MASTER__ID]), 'VW_Water'[Fiscal_Quarter]="Q1",'VW_Water'[CC_MAST_SUBTYPE]= "E*LEAK",'VW_Water'[EarlyClosure]= "Double up",'VW_Water'[Activity]= "Complete")

    Hope this helps.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Better you use Datesqtd or totalqtd with date table. Rest you can add to calculate

    Example

    QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))
trailing  4 QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-4,QUARTER))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/