topic Re: Measure taking on invalid values in DAX Commands and Tips

Measure taking on invalid values

jetform_consult — Mon, 27 Nov 2023 08:03:05 GMT

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.

Re: Measure taking on invalid values

jpessoa8 — Mon, 27 Nov 2023 08:57:17 GMT

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

Re: Measure taking on invalid values

jetform_consult — Mon, 27 Nov 2023 09:27:07 GMT

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.

Re: Measure taking on invalid values

jpessoa8 — Mon, 27 Nov 2023 10:38:38 GMT

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

Re: Measure taking on invalid values

jetform_consult — Wed, 29 Nov 2023 11:25:48 GMT

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.