Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@parry2k, Thank you. This code doesn't work. summary still shows nothing.

parry2k update, 04-May.png

 

The Pareto chart needs to present NC count by category. And date cannot be included because there is a many to one relationship between NC Num and date. A Pareto of Count(NC Num & Date) and category looks very different from a Pareto of NC count and category.

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

@AllisonKennedy, Thanks for clarifying. NC count works as-is. If summary is created in query editor will I be able to use the parameter-connected ShowNoShow field in NC records (added using DAX) to control which records are refelected in summary?

 

Re: Using ShowNoShow as measure in NC records means using SUMMARIZECOLUMNS to create summary won't work. Does this also apply to function SELECTCOLUMNS?

 

What I'm looking for is a way to use a What If parameter to control which records in table NC records are passed to table summary where summary contains Category and a count of NC Num and NC records contains Category, NC Num and Date.

@Anonymous  yes, since the ShowNoShow is now being created as a MEASURE it will still work even if you change how NC Count is calculated, or if you use my previous suggestion for NC Count as a MEASURE then it will automatically filter based on your date parameter (showNoShow) criteria without needing the showNoShow field/measure or column. You will simply need to add the NC Count measure to the table or matrix visualization along with the category from the summary table (again I would call this a dimension table rather than summary, but naming convention doesn't change the outcome). 

 


@Anonymous wrote:

 

 

Re: Using ShowNoShow as measure in NC records means using SUMMARIZECOLUMNS to create summary won't work. Does this also apply to function SELECTCOLUMNS?

 

 


Yes, using DAX ...COLUNMS function or any function which requires column as argument will not work with a MEASURE.

 

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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

@Anonymous question, why you are summarizing the table, try this though

 

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

 

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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

AllisonKennedy
Super User
Super User

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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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

 

parry2k
Super User
Super User

@Anonymous 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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.