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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hkillen
Helper I
Helper I

MAX Function disrupts Table Visaul

Hi.

I created a Measure that uses the MAX function when attempting to identify 

vPerentageOccupied= MAX ( [TableA] ! [PAX]) / MAX ( [TableA] ! [CAPACITY]) 

The Measure works just fine when the Filter Context is isolated to one ID value.

However, when adding the Measure to a Table Visual...a Table Visual with thousands OF ID values....the Table Visaul will not update...just spins round, and round, and round....

Can I use the MAX function in a Table Visual?  Should I use a different Function to accomplish the same task.

I understand I can create a Calculated Column in [TableA], but I believe Measures are more efficient.

 

Your thoughts?

 

Simple Example, for demonstation purposes.

TableA

[ID]  [PAX] [CAPACITY]

1       100      200

2       150     200

3       200     200

4       175    200

10 REPLIES 10
hkillen
Helper I
Helper I

I am trying to use a MEASURE in a Table Visual.

The Measure includes the MAX function, to isolate the ID from the Data Table (TableA)

Greg_Deckler
Super User
Super User

@hkillen What is that syntax? I tried that syntax and that did not work at all. Should be:

vPerentageOccupied= MAX ( 'TableA'[PAX]) / MAX ( 'TableA'[CAPACITY]) 

I don't see why that wouldn't work in a table where ID is included. 

I tried with this table:

Table = 
    ADDCOLUMNS(
        GENERATESERIES(1,10000),
        "PAX",RANDBETWEEN(0,175),
        "CAPACITY",200
    )

and a visual that included an unsummarized [Value] and the measure and got subsecond response time.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I'm referring to a Table Visual (i.e., Card, MultiCard, Matrix, Table)...not a Data Table imported or a Table created from DAX.

@hkillen ? I don't know what you mean by that. I have never seen that syntax in DAX or the ability to refer to a visualization in DAX so no idea what you mean.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I have a Table VISUAL...that'd I'd like to populate with Columns and Measures.

The Table VISUAL has displayed every ID.

I have no challenges adding Column (i.e. Fields) to the Table VISUAL.

A couple of Measures I'm attempting to add...I am having challenges.  The common denominator I have identified in the Measures is, perhaps, use of the MAX() Function.  Is there a different function I can used that will provide the values from an imported DATA table that align with the unique ID indicated in the VISUAL table?

@hkillen You could use MIN or LOOKUPVALUE maybe but what is your actual DAX code because I can be fairly certain that:

vPerentageOccupied= MAX ( [TableA] ! [PAX]) / MAX ( [TableA] ! [CAPACITY]) 

is not it. MAX is not a function that normally causes issues in DAX formulas. So, I have to believe that the issue you are facing is something else entirely. As my test indicated, I had a table of 10,000 unique ID's and a DAX formula with proper syntax returns in less than a second. So there is something else going on and in order to help you I need your actual DAX formula.

 

I understand from your capitalizations that you are annoyed and don't think I understand how visuals work. I ask you, how exactly else are you supposed to use a measure or column exactly? Of course you are using it in a VISUAL. I can assure you that I am not mystified by how you are using measures and columns, I am mystified by your syntax.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Here is the Measure syntax:

   -------------------------------------------------------

mOTP_Head_vCapacity =
// On-Time Performance / Headstart flight /
// A/C Capacity /
// {Actual}

//-----------------------------------------

VAR vResult =
MAX (
'tdDimAircraft_smy'[cCapacity] //#####
)

RETURN
vResult
--------------------------
When I place the Measure as a Field in the Table VISUAL, the Table VISUAL spins round and round and does not update

Therefore...could the issue be...not the Function so much as the Relationships?

I do have a lot of bi-diretional Relatoinships.  Could that be a driver?

@hkillen Absolutely. See the thread here for example: Re: DAX Head Scratcher - Microsoft Power BI Community. It's a long thread but it shows exactly what can happen in complex data models as well as how I "fixed" it in my case. You might try a MAXX(FILTER(...),...) to see if you can get around your problem. Adding a FILTER to my CALCULATE "magically" fixed my issue so I wonder if in your case doing something similar might work. It's hard to be specific because I have no idea what your data model looks like and what columns you have in your visual.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@hkillen I'm pretty sure I get that you are using this in a visual because nothing else makes any sense. So, next question what other tables/columns are used in that visual? Is everything coming from the tdDimAircraft_smy table or are there other tables involved? I aks because I have seen issues before in complex data models where you are trying to grab a column from another table and you end up in a situation where this generates a cartesian product of the two tables to get iterated over and that can majorly slow down visualization displays when you essentially generate a few million rows to iterate over for each and every row of the visual.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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