Latest Enterprise DNA Initiatives


DAX for a Dynamic Table instead of calculated table

Gents,

I need some help badly i have been stuck on this for a week now.

I need a way of making this dynamic so when i change the slicers at the top the table looks at the new mean and standard deviation values for that selection.

The way i do it now is using a calculated table which doesn’t update when slicers are changed therefore i need to re write this as a virtual table in DAX formula.

Normal Distribution =

//This is the code i was using. The issue with this is it doesn’t look at the filtered data it looks at the raw table.

//VAR MinNumber = FLOOR([X-3σ],1)
//VAR MaxNumber = CEILING([X+3σ],1)

//All i need is the info i see on my report page in the X-3σ and X+3σ cards. Obviously i typed this in to test but its perfect i just need to write this some how so it picks up those values. This is where i need your help.

VAR MinNumber = -231 // X-3σ card value
VAR MaxNumber = 1307 // X+3σ card value

RETURN
SELECTCOLUMNS (
CALENDAR ( MinNumber, MaxNumber ),
“X”, INT ( [Date] )
)

Sigma Test2.pbix (453.2 KB)

Bit confused by this one,

It’s just not entirely clear what you need unfortunately.

Can you be more specific around the exact formulas etc.

Like above you mentioned this is hardcoded but I’m seeing a measure?

image

It’s just not clear. I’m sure if this is laid out very clearly then it shouldn’t be too difficult.

Also the model here is a bit of a mess. You really want to clean this part up. It’s almost impossible to audit things when they look like this in the model

For all best practice tips on this see here.

Thanks
Sam

Hi Sam

I’m basically trying to make the x axis of the bell curve dynamic. The lowest number of the x axis range needs to be the value of x - 3 the highest number needs to be the value of the x +3 value in the report.

Regards

Dan

Ok makes sense.

Unfortunately is not possible to make an axis totally dynamic. This is due to the inability to use measures within an axis for a visual.

You can ofcourse create a calculated table that can be determined in size by the measures, BUT this only will update on refresh of your report. So I’m guessing this isn’t exactly what you’re looking for…or it might be?

You could maybe do this is a table only (but couldn’t be turned into a visual) by creating a measure that produced the dynamic X values. There’s obviously downsides to this and I presume this is likely not what you want anyway because the visual is the key with this sort of analysis.

Just also checking if you’ve had a chance to review the below.

Maybe some ideas here that could assist as other have posted about calculating similar things

Sam

Hi,

Yes i looked at that post and replied as i didn’t see the solution there either, its the exact same thing i am doing its the Normal deviation x axis needs to be made dynamic based on slicer selection.

I can get it all working fine as you can see in my PBIX the measure work fine in terms of std dev and mean. Only thing i cant do is get the result of the X-3 and X+3 value to be the start and end of the generated range for the bell curve table seems crazy its so close to working that we cant do that in power bi.

Id have thought it could be done using a virtual table and generate series some how but cant for the life of me figure out how.

Regards

Dan

Ok, yeah I just unfortunately think that Power BI in its current form doesn’t allow for a totally dynamic axis in a chart based on slicer selections.

It’s my understanding that this would only be possible by integrating the measure into a table created using DAX. And this would only update on refresh. This isn’t ideal I know, but still not everything is possible in Power BI yet.

Not sure if there is a custom visual maybe that allows this? I haven’t seen it, but it likely maybe someone has figured something out that sort of works.

Sam

Is there a way i can create a upper and lower limit what if parameter (2 boxes) and have that set the upper and lower limit ? a semi dynamic graph i could have then?

Because i have the values displayed visually via the measure just need the user to input the same values to the 2 what if parameter boxes? would this then auto update with refresh or that wouldn’t work either unless a refresh?

Just a thought

Dan

@Krays23,

Here are a couple of approaches to creating dynamic axes based on slicer values that might be worth taking a look at to see if they can be adapted to your specific context:

  • Brian

Hey Brian,

Had a look its not really what i need as everyone is dealing with calculated tables.

A need to generate the series via a virtual table so its fully dynamic, setting the upper and lower limits of the series using the X-3 and X+3 values you see in my screen shot

Dan

HI Just found the way to make it semi dynamic.

I used a what if parameter and between option, and set the range of the generated series to be the upper and lower limit of the slicer.

I then had power bi make all the Z calculations and FX values from the generated series.

Its good enough not fully dynamic but ok ! LOL

Be nice if there was a way to auto populate the slicer values ???

Sigma Test2.pbix (968.7 KB)

Cheers

Dan