cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

What If parameter value as scalar in condition for added column

I'm trying to use a What If parameter to allow users to change the date range of a line and stacked column chart. This is a Pareto chart of categories of NC records. There is no date column in the table the chart reflects (table "summary") because different categories of NC records can have the same date. Including dates would give counts of NC records by category and date. And the Pareto needs to show counts of NC records by category.

Table "summary" summarizes table "NC records."

summary = SUMMARIZECOLUMNS(
      'NC records'[Category], 
      FILTER('NC records','NC records'[ShowNoShow] = "Show"),
      "NC count", DISTINCTCOUNT('NC records'[NC Num])
)

Column 'NC records'[ShowNoShow] was added to provide a way to limit the date range in the Pareto chart. Table "NC records" contains a minimum of 12 months of records. But users will want to see Pareto'd data in shorter date ranges; last 30 days, last 7 days, yesterday, etc.

ShowNoShow = IF(DATEDIFF('NC records'[Date],DATE(2020,4,22),Day) < 7,"Show","NoShow")

For this post, the ShowNoShow column labels NC records "Show" if their dates are in the 7 days ending 22-Apr-2020. In practice Today() is substituted for DATE(2020,4,22).

In an attempt to change a hard-wired 7-day range to a range users can control, I created the What If parameter, "Date Parameter." Date Parameter is added to a slicer and Date Parameter Value is substituted for the scalar 7 in the ShowNoShow expression.

ShowNoShow = IF(DATEDIFF('NC records'[Date],DATE(2020,4,22),Day) < SELECTEDVALUE('Date Parameter'[Date Parameter]),"Show","NoShow")

But SELECTEDVALUE('Date Parameter'[Date Parameter]) is apparently 0 becasue the Pareto chart now shows nothing.

What am I missing?

 

The Pareto chart scalar set to 1000 to show all data.

 

4.png

 

A separate page with table of both tables where scalare is set to 7. Works fine.

 

1.png

 

Same page with scalar set to SELECTEDVALUE. Both summary table and chart show nothing.


2.png

 

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: What If parameter value as scalar in condition for added column

@PBIuseNpa try following, add it as a measure

 

ShowNoShow = 

IF(DATEDIFF(MAX('NC records'[Date]),DATE(2020,4,22),Day) < SELECTEDVALUE('Date Parameter'[Date Parameter]),"Show","NoShow")

 

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Champion
Community Champion

Re: What If parameter value as scalar in condition for added column

Parry2k beat me to it, but the issue here isn't that the Parameter value is 0, as you can see and have shown in the tables you created, the parameter value is displayed correctly has 7, and is a measure. In order for your calculation to work, you need to use a measure, not a calculated column, which will provide the dynamic values you're looking for. 

 

An alternative option to the solution Parry2k posted is to use the COUNTROWS function in combo with the FILTER you're already using, which means you would no longer need the summary table for this calculation. Try creating the following MEASURE:

NC Count :=
COUNTROWS (
FILTER (
'NC records',
DATEDIFF ( 'NC records'[Date], TODAY (), DAY ) < [Date Parameter Value]
)
)

 

If that helps, please mark it as a solution.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


https://sites.google.com/site/allisonkennedycv

Highlighted
Helper I
Helper I

Re: What If parameter value as scalar in condition for added column

@parry2k, thanks for looking into this. Unfortunately, this solution returns no data in table summary.

 

parry2k solution NG.png

 

I tried an alternate by substituting the Date Parameter Value measure.

ShowNoShow = IF(DATEDIFF(MAX('NC records'[Date]),DATE(2020,4,22),Day) < [Date Parameter Value],"Show","NoShow")

But still no luck.
parry2k alternate NG.png

 

I'm stumped.

Highlighted
Helper I
Helper I

Re: What If parameter value as scalar in condition for added column

@AllisonKennedy, Thanks for this response. I need the summary table. The Pareto chart not only presents NC count, it also presents the cumulative total of NC count as a percent of total NC count. The Pareto chart requires a) NC count by category, b) the categories of these NC counts and c) the cumulative percent. To get cumulative percent I need three measures and two added columns.

 

The three measures...

Cumulative Percent = [Cumulative Total] / [Total NC count]

 

Cumulative Total = CALCULATE(SUM('summary'[NC count]), FILTER( ALLSELECTED('summary'), 'summary'[Rank] <= MAX('summary'[Rank])))

 

Total NC count = CALCULATE(SUM('summary'[NC count]), ALLSELECTED('summary'))

 

...and the two added columns.

 

Rank = RANKX('summary', 'summary'[NC count] + (INT('summary'[random number by Category]) / 100000))

 

random number by Category = RANKX(ALL('summary'),FIRSTNONBLANK('summary'[Category],'summary'[Category]),,ASC,Dense)

 

Highlighted
Community Champion
Community Champion

Re: What If parameter value as scalar in condition for added column

@PBIuseNpa  can you confirm that you have tried @parry2k 's solution as a MEASURE? You were originally using a column, but if you use a measure, it should display the results you're after once you put it in the table visualization.

 

Alternatively, I suggest you delete the NC Count column from the summary table. You can keep the summary table, in fact the Category column is very useful and I would personally call it a dimension table. It just doesn't need to be referenced for the NC count. So a better way to create the summary table would be to create it in the query editor as a pre-loaded table with each category listed once. The NC count does not need to be a column (as it is dynamic and should change based on slicers and data) but a measure: 

 

NC Count:=
COUNTROWS (
FILTER (
'NC records',
DATEDIFF ( 'NC records'[Date], TODAY (), DAY ) < [Date Parameter Value]
)
)

 

Please let me know if any of that doesn't make sense. 

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


https://sites.google.com/site/allisonkennedycv

Highlighted
Helper I
Helper I

Re: What If parameter value as scalar in condition for added column

@AllisonKennedy, Again, thanks. I responded to @parry2k's post. That suggestion didn't work. Re: Deleting NC count. I don't know how to Pareto NC count by category without using NC count.

Highlighted
Super User IV
Super User IV

Re: What If parameter value as scalar in condition for added column

@PBIuseNpa I'm not fully sure from your reply if you added following as a measure or not, it has to be a MEASURE

 

ShowNoShow = IF(DATEDIFF(MAX('NC records'[Date]),DATE(2020,4,22),Day) < [Date Parameter Value],"Show","NoShow")





Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper I
Helper I

Re: What If parameter value as scalar in condition for added column

@parry2k, Thanks for clarifying. This works as far as connecting the parameter to table NC records. Thanks very much.

 

corrected NC records.png

 

But this doesn't cascade to table summary. Summary still shows nothing.

 

summary = SUMMARIZECOLUMNS(
      'NC records'[Category], 
      FILTER('NC records',[ShowNoShow] = "Show"), 
      "NC count", DISTINCTCOUNT('NC records'[NC Num]))

 

 

Is this code correct?

Highlighted
Community Champion
Community Champion

Re: What If parameter value as scalar in condition for added column

@PBIuseNpa  The code for summary will not work anymore now that ShowNoShow is a MEASURE and not a column. Please try my suggestion from previous post to delete NC Count column and create NC count as a measure instead. I also highly recommend having the Summary table built into the query rather than using DAX to create this table. Do your categories change often? If not, you can simply reference them from a static table. Let us know what your skill level and experience on Query Editor is and we can support you to implement these suggestions. 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


https://sites.google.com/site/allisonkennedycv

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors