Forum Discussion

hkillen's avatar
hkillen
Icon for Helper I rankHelper I
5 years ago

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 Fil...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

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

hkillen's avatar
hkillen
Icon for Helper I rankHelper I
5 years ago

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
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

  • hkillen's avatar
    hkillen
    Icon for Helper I rankHelper I
    5 years ago

    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?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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.