cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
RichardJ
Responsive Resident
Responsive Resident

DAX Measure for a COUNT function syntax

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

 

 

Measure_Question.PNG

 

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

 

 

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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

View solution in original post

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
RichardJ
Responsive Resident
Responsive Resident

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

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Measure_Question_solved.PNG

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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
Responsive Resident
Responsive Resident

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

RichardJ_0-1680437131525.png

 

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors