Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Sam Duval]

Is it possible to create a filer value that would be set at one value when a PBI report first loads but is easily overridden by a users subsequent selections?  

I have a report with 5 visuals on it, and i’m wanting 2 of the 5 to only show the previous month when the report first loads. I don’t want to hard set the slicer value, as i want the 3 other visuals to show all the month ends i have in the dataset. I’ve tried a couple iterations of ifs and switchs but can’t quite get the logic right in my head of how to go about it. I can’t do calculated columns because it has to be overwritten if the user chooses a different date from the slicer. 

any ideas?

——————————
Sam Duval
Data Quality Analyst
Indianapolis
——————————

[Cekou C.]

Hi Sam,

So I assume that you will have a date slicer set at the current/last month of the visual filtering what you call “the 3 other visuals”

And you will have two other visuals for which you only want to display the previous month figures at loading phase but you also wich to display the current month/last month figures if the user desires to?

So what I’m thinking about is using SELECTEDVALUE logic with a text slicer that will control these 2 specific visuals. The SELECTEDVALUE measure would SWITCH between PREVIOUSMONTH value and the current value (which is used by the 3 others visual) depending on what is selected in the text slicer.

Hopefully this will help.

——————————
Cekou C.
Senior Accountant
——————————
——————————————-

[Sam Duval]

Thanks for the response I have a date slicer yes, but at loading it want it to have no value so the other 3 visuals (multi row cards) show 13 months of history. Then when the user chooses a date from the date slicer, Im trying to get all 5 visuals to filter by the choose date.

——————————
Sam Duval
Data Quality Analyst
Indianapolis
——————————
——————————————-

[Cekou C.]

Ok, then you can use ISFILTERERD in a IF statement.

Something like :
IF(
  ISFILTERERD ( DateTable[MonthColumn] );
  [ThisValue];
  [ThatValue]
)

You will put this measure in the value field of each of the visuals that you want them to react with the month selected in the slicer

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-

[Cekou C.]

You may need to tweak between ISFILTERERD and ISCROSSFILTERED depending on the date column that appears in the slicer (dates, months etc.)

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-

[Sam Duval]

Thanks for the tip I’ll give it a try tonight. The is filtered might be the function I was missing with my attempts at nesting various formulations of switches and ifs

——————————
Sam Duval
Data Quality Analyst
Indianapolis
——————————
——————————————-

[Sam Duval]

Here is what I plugged in for the filter, seems simple enough. 

Default_Chart_Filter = IF(

ISFILTERED(Dates[Date])=FALSE(),

[Prev_Month]

,SELECTEDVALUE(Dates[Date])

)

The problem lies in the visuals I am trying to filter are stacked bar charts, and I don’t think i am able to add a measure to the values of this chart. I’ve tried a couple other dax filtering commands but keep coming up with unexpected, error or no results.. I think i need this to somehow resolve to 1/0 where 1 is either the current month or the selected month from the slicer

——————————
Sam Duval
Data Quality Analyst
Indianapolis
——————————
——————————————-

[Cekou C.]

Hi Sam,

Your ISFILTERERD measure does not seem to be exactly right to me. I’m not saying that it is wrong either, but I wouldn’t have written it this way.

I assume [Prev_Month] is a measure. You placed it the second argument of the IF statement. However you placed a column reference in the third argument SELECTEDVALUE( Dates[Date] ). I would have expected it to be a measure instead.

Could you please share a dummy version of your .pbix file with false data? Or at least a screenshot of your report page? I’d like to see your date table, the date column used in the slicer and the measures currently being used in the five visuals you are talking about.

Cheers,

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-

[Sam Duval]

It might be written different but appears to work, as it gives me the date value I was expecting to see. But I can’t add it to the values of my chart. I am also playing with a filtered measure instead of using the column of loan amount, I am trying to build a dynamically filtered table that would show the default value of previous month, or the selected value. I think I have an idea how to go about it but i haven’t hammered it out quite yet.

thanks for the help

——————————
Sam Duval
Data Quality Analyst
Indianapolis
——————————
——————————————-

[Sam Duval]

Ok I was actually able to figure it out tonight. Using your idea of the isfiltered to create my default date, and some calculate table filter trickery I was able to create a measure that gives me the numbers I need:

Filtered_Total_Loan_Amount = var __Default_Date =[Default_Chart_Filter]

return

CALCULATE(sum(Lending_LFC_Funded_Loans_Report[Total_Funded_Amount]),Lending_LFC_Funded_Loans_Report[Month_of_Funding]=__Default_Date)

——————————
Sam Duval
Data Quality Analyst
Indianapolis
——————————
——————————————-

[Cekou C.]

Very good @Sam Duval, nice job! 😉

——————————
Cekou C.
Accounting Supervisor & PBI Enthusiast
——————————
——————————————-