Quarterly Count with multiple filters

[chinthada]

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

[Ashish_Mathur]

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.

[StevenGrenier · Accepted solution]

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!

[chinthada]

Thanks @StevenGrenier 

As suggested adding more FILTER statements has done the trick!

[amitchandak]

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

[amitchandak]

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/