cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bdmichael09
Helper II
Helper II

Multiple SELECTEDVALUES conflicting for dynamic MoM Change

It's probably easiest to explain this by starting with an image first
image.png

What I'm tryin to do is have each one of these lead counts filter to the selected value of the month above them, which are currently set up in 3 separate datedim tables. Then what I'm trying to do is use those calculations to get the % change in the card visual on the far right. 

My Lead Count cards are presently using variations of this measure and the output what I need:

Lead Count = 
var monthtouse = SELECTEDVALUE(TableDateDim[MonthAbbrvAndYearName])
var maxdate = calculate(max(AlternateDateDim1[DateSK]),AlternateDateDim1[MonthAbbrvAndYearName] = monthtouse)
var mindate = calculate(min(AlternateDateDim1[DateSK]),AlternateDateDim1[MonthAbbrvAndYearName] = monthtouse)
var leadcount = calculate(DISTINCTCOUNT(Table[IdentifierDim]),Table[CreateDtID] >= mindate,Table[CreateDtID] <= maxdate)
return leadcount

 The issue I've run into that I cannot figure out a solve for is the card for the MoM Change will not work without allowing interactions with the slicers but the second I allow both slicers to interact with the card they conflict and the card outputs (blank). Currently the measure I'm using is very simple but apparently won't work like I had hoped

Leads MoM Change = 
var leadsrecent = [Lead Count]
var leadsother = [Lead Count2]
var output = divide(leadsrecent-leadsother,leadsother)
return output


Is there some creative way to get to my end that I just haven't thought of or seen before? I'm about at my wits end.

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @bdmichael09 ,

 

SELECTEDVALUES doesn’t support multiple values. So you can use VAUES function.

The lead count measure as following,

 

New lead count = 
var monthtouse = VALUES(AlternateDateDim1[MonthAbbrvAndYearName])
var maxdate = calculate(max(AlternateDateDim1[DateSK]),AlternateDateDim1[MonthAbbrvAndYearName] in monthtouse)
var mindate = calculate(min(AlternateDateDim1[DateSK]),AlternateDateDim1[MonthAbbrvAndYearName] in monthtouse)
var leadcount = calculate(DISTINCTCOUNT(Data[Identifier]),Data[CreateDtID] >= mindate,Data[CreateDtID] <= maxdate,USERELATIONSHIP(AlternateDateDim1[DateSK],Data[CreateDtID]))
return leadcount

 

M1.jpg

 

M2.jpg

 

The reason why MoM displays blank is the interactions. Please check the interactions between slicers and card visuals.

The following is the result of our change interaction, is it what you want?

 

M3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

7 REPLIES 7
v-zhenbw-msft
Community Support
Community Support

Hi @bdmichael09 ,

 

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-zhenbw-msft
Community Support
Community Support

Hi @bdmichael09 ,

 

SELECTEDVALUES doesn’t support multiple values. So you can use VAUES function.

The lead count measure as following,

 

New lead count = 
var monthtouse = VALUES(AlternateDateDim1[MonthAbbrvAndYearName])
var maxdate = calculate(max(AlternateDateDim1[DateSK]),AlternateDateDim1[MonthAbbrvAndYearName] in monthtouse)
var mindate = calculate(min(AlternateDateDim1[DateSK]),AlternateDateDim1[MonthAbbrvAndYearName] in monthtouse)
var leadcount = calculate(DISTINCTCOUNT(Data[Identifier]),Data[CreateDtID] >= mindate,Data[CreateDtID] <= maxdate,USERELATIONSHIP(AlternateDateDim1[DateSK],Data[CreateDtID]))
return leadcount

 

M1.jpg

 

M2.jpg

 

The reason why MoM displays blank is the interactions. Please check the interactions between slicers and card visuals.

The following is the result of our change interaction, is it what you want?

 

M3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

View solution in original post

Ashish_Mathur
Super User III
Super User III

Hi,

Why do you need 4 drop downs?  Why 4 date tables?  Try this:

  1. Create a Calendar Table and build a relationship from the Date column of your data table to the Date column of your Calendar Table
  2. In the Calendar Table, write calculated field formulas to extract Year, Month Name and Month number.  Sort the Month Name by the Month number
  3. To your visuals/slicers, drag Year/Month from the Calendar Table.  Select the Year as 2020 and Month as August
  4. Write these measures

Lead count = 

 

DISTINCTCOUNT(Table[IdentifierDim])

 

Lead count in previous month

 

calculate([lead count],previousmonth(calendar[date]))

Lead count in previous month

calculate([lead count],sameperiodlastyear(calendar[date]))

Growth in lead count over previous month (%)

divide(([lead count]-[lead count in previous month]),[lead count in previous month])

Growth in lead count over same period last year (%)

divide(([lead count]-[lead count in same period last year]),[lead count in same period last year])

 


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

@Ashish_Mathur I have a calendar table with every possible calendar field you could want, trust me, and I already had a relationship tied between the data table and the date table. I also initially only had the 1 date table but I tried creating the additional ones as a hail mary to solve the problem but it's not the date tables causing my problem.

 

I've got the page set up to look like its 'This month' compared to 'last month' but I would like to be able to compare months at will, hence the use of the selectedvalue() function with the months in the slicers. Without the desire to be able to compare months at will, I probably would have done exactly what you suggested. If I absolutely have to hard code then I'll hard code, but I would prefer a complex or creative solution if it enables flexibility in the end. 

Hi,

In that case, you will have to create inactive relationships between your base dataset and other calendar tables and then use the USERELATIONSHIP() function to compare months of your choice.


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

@Ashish_Mathur I had tried both active and inactive relationships with those tables. I just tried including USERELATIONSHIP() and that also doesn't seem to be getting things to work as I would like them to. Either something is just eluding me or that also is not a solution to the problem. I've uploaded a trimmed down version of my .pbix file that should include the necessary tables, fields, and measures in case that is helpful.

 

https://www.dropbox.com/s/jn0wfd1qokk5mfu/CSC%20Dash2.pbix?dl=0

Hi,

In the image, you can see that the cards where the filter criteria is August 2020 are working fine.  Create the AlternateDateDim tables the same as the DateDim table and create the same relationship that i have.  Then write three measures (the way i have create the "Measure" measure).

Hope this helps.

You may download the PBI file from here.

Hope this helps.

Untitled.png


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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors