Help on DAX

[admin_xlsior]

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) :

ITEM	INVOICEDATE	As of date	Invoice age
P-100082	1/25/2016 0:00	1/4/2016 0:00	21
P-100083	1/25/2016 0:00	1/4/2016 0:00	21
P-100088	1/25/2016 0:00	1/4/2016 0:00	21
P-100249	2/8/2016 0:00	1/4/2016 0:00	35
P-100263	3/17/2016 0:00	1/4/2016 0:00	73
P-100264	3/17/2016 0:00	1/4/2016 0:00	73
P-100265	3/17/2016 0:00	1/4/2016 0:00	73
P-100266	3/17/2016 0:00	1/4/2016 0:00	73
P-100267	3/17/2016 0:00	1/4/2016 0:00	73
P-100359	3/14/2016 0:00	1/4/2016 0:00	140
P-100360	3/14/2016 0:00	1/4/2016 0:00	140

I also have a Range Table like this :

Name	From	To
<=30 days	0	30
31-90 days	31	90
> 90 days	91	9999

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 :

ITEM	INVOICEDATE	As of date	Invoice age	Aging interval
P-100082	1/25/2016 0:00	1/4/2016 0:00	21	<= 30 days
P-100083	1/25/2016 0:00	1/4/2016 0:00	21	<= 30 days
P-100088	1/25/2016 0:00	1/4/2016 0:00	21	<= 30 days
P-100249	2/8/2016 0:00	1/4/2016 0:00	35	31 - 90 days
P-100263	3/17/2016 0:00	1/4/2016 0:00	73	31 - 90 days
P-100264	3/17/2016 0:00	1/4/2016 0:00	73	31 - 90 days
P-100265	3/17/2016 0:00	1/4/2016 0:00	73	31 - 90 days
P-100266	3/17/2016 0:00	1/4/2016 0:00	73	31 - 90 days
P-100267	3/17/2016 0:00	1/4/2016 0:00	73	31 - 90 days
P-100359	3/14/2016 0:00	1/4/2016 0:00	140	91 - 150 days
P-100360	3/14/2016 0:00	1/4/2016 0:00	140	91 - 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 days	31 - 90 days	91 - 150 days
P-100082	1
P-100083	1
P-100088	1
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,

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[admin_xlsior]

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.

Thanks,

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

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]

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,