I have a Measure called [SelectedDate].
```
SelectedDate = IF(SELECTEDVALUE(OrdersByCalendar_DTL[OrderDate] )=Blank();Blank();SELECTEDVALUE ( OrdersByCalendar_DTL[OrderDate]))
```
I am attempting to use it to make a graph displaying Month totals disappear when a date is selected from a slicer...PowerBI-101.
```
MonthCol1 = IF([SelectedDate]="Blank"; OrdersByCalendar_DTL[CNT Date(SUM) per Period]; Blank())
```
It worked until I switched the slicer to another slicer and now the measure seems to be returning PHANTOM values which is causing my IF([SelectedDate]=Blank();sum(Column1);Blank()) to ALWAYS return a value.
I only found this out (after pulling out half my hair) by switching the graph to a table and adding a CARD that displays the [SelectedDate] value. Low and behold, [SelectedDate] is populated with values in the table whilst the CARD clearly shows [SelectedDate] to be (Blank). Another strange fact is that while each date's MonthCol1 value is Blank() (as per the measure), the Total at the bottom is the correct tally for the month.
Any ideas?🙏
I've tried adding the measure to the graph's filter section and selecting IS BLANK...The graph now shows absolutely nothing even when selecting a date. This is due to (I believe) the [SelectedDate] measure taking on un-authorized date values as shown in the screen shot.
1 ACCEPTED SOLUTION
Hi @jetform_consult ,
The measure is not returning phantom values, it's only working on a different logic then what you are expecting.
For each row of that matrix, having the context of the OrdersByCalendar_DTL[OrderDate] field, the measure is doing the correct calculation. The issue is on the "total" level, because in there the DAX doesn't have the context of that OrdersByCalendar_DTL[OrderDate] field and is doing a calculation based on an aggregation of that field (if you see on your matrix there is a (blank) value for the "total" level of your SelectedDate measure, which "triggers" the [CNT Date(SUM) per Period] for the "total" of the MonthCol1 measure).
Since you want a measure calculation that, on the "total" level, still takes in consideration the OrdersByCalendar_DTL[OrderDate] field, you need to wrap your [MonthCol1] measure on a iterator that does the calculation for each row and afterwards does a aggregation. Something like this:
MonthCol1 =
SUMX(VALUES(OrdersByCalendar_DTL[OrderDate]);
IF([SelectedDate]=BLANK(); OrdersByCalendar_DTL[CNT Date(SUM) per Period]; Blank())
)
Based on a dummy dataset I've created, this is the behaviour of your measure and my suggestion with SUMX:
Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudo 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Jorge Pessoa
Thank you Jorge for your response.
Unfortunately, this is not what I am attempting to do. I only switched to a table to try to get an understanding of what was happening.
What I am trying to do is to have 2 graphs superimposed on top of each other. One graph will have MONTH values and the other graph will have the values for the date contained in [SelectedDate]. When no date is selected, my other measure (OrdersByCalendar_DTL[CNT Time(SUM) per Period]) is Blank(). If you select a date, [SelectedDate] takes on that value and OrdersByCalendar_DTL[CNT Date(SUM) per Period] takes on a value.
This is supposed to make one graph ALWAYS show Blank() values and allows you to "seemingly" switch between 2 different graphs...One for the entire month, and the other for just a single day.
This use to work until I changed my date slicer from a PowerSlicer to a PivotSlicer because of other issues with the PowerSlicer.
What I want is the following:
No Date selected...Should show the entire Month
When you select a date...The Month graph should be flooded with Blank() values making it transparent to reveal the TOD graph beneath it.
