Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
6 years ago
Solved

Help on DAX

Hi guys,

 

Need some help on my visualisation. I have this data which is an Item List, with its invoice date then a "age" time between that invoice data and my input date (lets call it As Of Date) :

ITEMINVOICEDATEAs of dateInvoice age
P-1000821/25/2016 0:001/4/2016 0:0021
P-1000831/25/2016 0:001/4/2016 0:0021
P-1000881/25/2016 0:001/4/2016 0:0021
P-1002492/8/2016 0:001/4/2016 0:0035
P-1002633/17/2016 0:001/4/2016 0:0073
P-1002643/17/2016 0:001/4/2016 0:0073
P-1002653/17/2016 0:001/4/2016 0:0073
P-1002663/17/2016 0:001/4/2016 0:0073
P-1002673/17/2016 0:001/4/2016 0:0073
P-1003593/14/2016 0:001/4/2016 0:00140
P-1003603/14/2016 0:001/4/2016 0:00140

 

I also have a Range Table like this :

NameFromTo
<=30 days030
31-90 days3190
> 90 days919999

 

Then for the clasification of the age, I create a measure like below :

Aging interval = CALCULATE(
                  VALUES('Range[Name]),
                  FILTER(
                     'Range',
                     'Items'[Invoice age] >= 'Range [FROM] &&
                     'Items'[Invoice age] < 'Range'[TO]
                                    )
)

 

If I create Table Visualization, it looks correct :

ITEMINVOICEDATEAs of dateInvoice ageAging interval
P-1000821/25/2016 0:001/4/2016 0:0021<= 30 days
P-1000831/25/2016 0:001/4/2016 0:0021<= 30 days
P-1000881/25/2016 0:001/4/2016 0:0021<= 30 days
P-1002492/8/2016 0:001/4/2016 0:003531 - 90 days
P-1002633/17/2016 0:001/4/2016 0:007331 - 90 days
P-1002643/17/2016 0:001/4/2016 0:007331 - 90 days
P-1002653/17/2016 0:001/4/2016 0:007331 - 90 days
P-1002663/17/2016 0:001/4/2016 0:007331 - 90 days
P-1002673/17/2016 0:001/4/2016 0:007331 - 90 days
P-1003593/14/2016 0:001/4/2016 0:0014091 - 150 days
P-1003603/14/2016 0:001/4/2016 0:0014091 - 150 days

 

Question is why I cannot put the Aging interval Measure to COLUMNS by using Matrix Visualization ?

 

I want to create something like this :

ITEM<= 30 days31 - 90 days91 - 150 days
P-1000821  
P-1000831  
P-1000881  
P-100249 1 
P-100263 1 
P-100264 1 
P-100265 1 
P-100266 1 
P-100267 1 
P-100359  1
P-100360  1

 

Can someone help or advice what should I do ?

 

Thanks,

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    The AND() function takes 2 inputs.  Only when both inputs evaluate to TRUE will the AND() function also evaluate to TRUE. So if the Age of the invoice is >= min value and also <= max value, then it means that the Age of invoice falls between the min and ma values.  The AND() function will therefore evaluate to TRUE.  To convert TRUE to 1, one can multiply with 1.

    Hope this clarifies.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi!

    You can simplified this by creating a calculated column in your "items" table.

    Create the calculated column with the next dax formula in your "items" table: 

    INTERVAL = IF(ITEMS[Invoice age]>=0,IF(ITEMS[Invoice age]<=30,"<= 30 days",IF(ITEMS[Invoice age]<=90,"31 - 90 days","> 90 days")))
    With this you can create the matrix you want:
     
     
    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy
      6 years ago

      Hi, 

      Sorry, I forgot to tell, I also need the range to be from a table. And what I know is I need to use CALCULATE to do that, however there is another stopper, I cannot use CALCULATE since I'm using Direct Query.

       

      So that is the background story why I create a measure. (CALCULATE is not allowed for CalculatedColumn)

       

      Thanks,

    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy
      6 years ago

      Hi Ashish,

       

      It works!!

      I saw the measure, honestly I don't realy understand how it works ?

      Measure = 1*(AND([Age of invoice]<=MAX('Ageing buckets'[To]),[Age of invoice]>=MIN('Ageing buckets'[From])))

       

      Can you be so kind to explain a bit what's the logic in there? Sorry, but I'm really a beginner in DAX. I did some DAX material, but that one, is fantastic yet not easy for me to know what's going on.

      :manembarrassed:

       

      Thanks,

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        The AND() function takes 2 inputs.  Only when both inputs evaluate to TRUE will the AND() function also evaluate to TRUE. So if the Age of the invoice is >= min value and also <= max value, then it means that the Age of invoice falls between the min and ma values.  The AND() function will therefore evaluate to TRUE.  To convert TRUE to 1, one can multiply with 1.

        Hope this clarifies.