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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Continued Contributor
Continued Contributor

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors