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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
jetform_consult
Frequent Visitor

Measure taking on invalid values

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.

 

jetform_consult_0-1701071942977.png

 

 

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

Sorry for the misunderstanding @jetform_consult.

 

At the moment what is the behaviour on the Month graph? Can you provide a print? I couldn't understand from your explanation


Best regards,
Jorge Pessoa

View solution in original post

4 REPLIES 4
jetform_consult
Frequent Visitor

Hi Jorge...Thanks for your valuable time.

 

I believe I found the issue.  The problem only reared its ulgy head when I attempted to clean up (what I thought were) unneeded tables from 5 downto 3.  It was then the relations between the new consolidated tables and the rest of the tables that 'somehow' caused the measures to misbehave.

 

I simply went back to the original table format, cleaned up a few unneeded relationships with the new tables, and everything started working again.

 

Thanks once again for your tips and your time.

jpessoa8
Super User
Super User

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:

 

jpessoa8_0-1701075318000.png

 

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

jetform_consult_0-1701077009802.png

 

When you select a date...The Month graph should be flooded with Blank() values making it transparent to reveal the TOD graph beneath it.

 

jetform_consult_1-1701077210866.png

 

Sorry for the misunderstanding @jetform_consult.

 

At the moment what is the behaviour on the Month graph? Can you provide a print? I couldn't understand from your explanation


Best regards,
Jorge Pessoa

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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