DAX Measure for a COUNT function syntax

[RichardJ]

Hi,

I'm using a parameter 'Target' to determine a Target

1 can be changed to a chosen value

2 updates with a status depending on whether the 'Actual' Values match the Target Values and generate a Delta Status

so far so good

Am having trouble with the approach/syntax for 3 as I don't have a 'table' to obtain the data from.

I'd like the measure to count the number of rows in table 'A' which have a 'Delta Status' of 'Met Plan'

The table doesnt exist as a searchable entity as it's only generated when the measures are added to it. Am guessing I need to generate an equivalent table within a measure?

What's the best method of fixing the measure '3' so that this displays the number of rows from Table A where the Location per week number has a Delta Status of 'Met Plan'

Normally spend most of my time in PowerQuery and this is a first attempt at using parameters so would appreciate any guidance/help.

The PBIX can be found here : https://www.dropbox.com/s/u5ompdq83ahw68m/Measure_Syntax_Questions_Parameters.pbix?dl=0

The measure i'd appreciate help with is 'Total Met Plan'

Thanks

Richard

[lbendlin · Accepted solution]

Total Met Plan = 
var a = SUMMARIZE(Actuals_By_Location,Actuals_By_Location[Location],Actuals_By_Location[Week Number],"d",[Delta Status])
return countrows(filter(a,[d]="Met Plan"))

[RichardJ]

@Ashish_Mathur @lbendlin Thanks a lot for the responses. I'd never used GENERATE or SUMMARIZE before so appreciate the lesson.

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

Total Met Plan = COUNTROWS(FILTER(GENERATE(VALUES(Actuals_By_Location[Location]),VALUES(Actuals_By_Location[Week Number])),[Delta From Plan]>=0))

Hope this helps.

[RichardJ]

Thanks Ashish for the response. This works perfectly

File includding your formula now uploaded to dropbox

https://www.dropbox.com/s/uugo5zlsiwxfk0t/Measure_Syntax_Questions_Parameters.pbix?dl=0

[Ashish_Mathur]

You are welcome.

[lbendlin · Accepted solution]

Total Met Plan = 
var a = SUMMARIZE(Actuals_By_Location,Actuals_By_Location[Location],Actuals_By_Location[Week Number],"d",[Delta Status])
return countrows(filter(a,[d]="Met Plan"))

[RichardJ]

Thanks lbendlin for the response. This works perfectly

File including your formula now uploaded to dropbox

https://www.dropbox.com/s/uugo5zlsiwxfk0t/Measure_Syntax_Questions_Parameters.pbix?dl=0