topic Re: Dax for Excel Index Match in DAX Commands and Tips

Dax for Excel Index Match

satich989 — Tue, 08 Dec 2020 16:41:04 GMT

I have products data, can someone advise me to get this excel function into powerbi. I am looking for the dax equivalent of this. Please let me know if any other details are needed from me to make this perhaps more clear.

Here is the link for the pbix file https://www.dropbox.com/s/5g63f1gucs30kuu/Product%20Excel.pbix?dl=0

This is the data

Product	Ordered	Shipped	Delivered	Returned	led
A	1-Apr-20	2-Apr-20	1-Jan-00	1-Jan-00	1-Jan-00
B	3-Jan-20	6-Jan-20	16-Mar-20	17-Mar-20	1-Jan-00
C	6-Apr-20	1-Jan-00	1-Jan-00	1-Jan-00	12-Aug-20
D	3-Jan-20	1-Jan-00	1-Jan-00	1-Jan-00	6-Jan-20
E	3-Feb-20	6-Feb-20	16-Apr-20	17-Aug-20	1-Jan-00

Re: Dax for Excel Index Match

wdx223_Daniel — Tue, 08 Dec 2020 05:43:43 GMT

you need to unpivot your fact table, and create a date table. then put the product field in the row and date in the column of a matrix visual. create a measure like below, and put it in the value area.

=VAR _maxdate=MAX(facttable[date]) RETURN IF(MAX(datetable[date]>=_maxdate,CALCULATE(MAX(facttable[status]),factable[date]=_maxdate))

Re: Dax for Excel Index Match

satich989 — Tue, 08 Dec 2020 15:59:46 GMT

Hi Daniel

Thank you for the suggestion. Did that and the chart on the right is what I see now. Happy that we got the latest/max status per month, shown in this image.

PBIX Link - https://www.dropbox.com/s/5g63f1gucs30kuu/Product%20Excel.pbix?dl=0

It doesnot show blank months in between and the exact thing I am looking for is the count of status per month as seen here in the excel. Any suggestions on how to tweak your measure or anything that needs to be done differently?

Re: Dax for Excel Index Match

Ashish_Mathur — Wed, 09 Dec 2020 00:07:01 GMT

Hi,

What is the final result you want?

Product wise status everyday (the image you showed in the original post); OR
Count of products everyday [without products] (image you have shown in your last post)

Please clarify.

Re: Dax for Excel Index Match

wdx223_Daniel — Wed, 09 Dec 2020 00:23:06 GMT

satich989 you need to break the relationship betwee Table and Date

or update your code like this

M = VAR _maxdate = CALCULATE(MAX('Table'[Value]),ALL('Date')) RETURN IF(MAX('Date'[Date]) >=_maxdate, CALCULATE(MAX('Table'[Attribute]),'Table'[Value] =_maxdate,ALL('Date')) )

Re: Dax for Excel Index Match

satich989 — Wed, 09 Dec 2020 02:00:36 GMT

Hi Ashish

Thanks for looking into this problem.

The second is what I finally want - count of status by MONTH. I thought if I could get to the original image, then I can do a count on the status to reach my final requirement.

Final requirement

Re: Dax for Excel Index Match

satich989 — Wed, 09 Dec 2020 01:57:41 GMT

Tried both of those approaches Daniel and didnot work. This image shows when I removed the date relationship and click filtered on Product B and the chart on right does not show Jan and Feb.

Then I enabled 'show items with no data' on the month name and it shows the months but shows them as blank instead of 'Ordered'.

Re: Dax for Excel Index Match

wdx223_Daniel — Wed, 09 Dec 2020 05:14:17 GMT

you need a real date table with all dates

Re: Dax for Excel Index Match

satich989 — Wed, 09 Dec 2020 17:18:52 GMT

Awesome, thank you Daniel.

Apolgy for another question - to finsh my analysis I am looking to create a stacked bar chart that shows the latest status in a month and adds that status to next months if blank until there is a new status.

As example - for Product E, it should show

shipped in February and March

delivered in April, May

returned in June until current month (in this case june,)July, August, Sept, Oct, Nov, Dec

When not filtered for a product, it shows total

Re: Dax for Excel Index Match

wdx223_Daniel — Thu, 10 Dec 2020 01:18:47 GMT

satich989 is this what you want?

https://www.dropbox.com/s/ki2nud7fv63r53j/Product%20Excel%20%282%29.pbix?dl=0

Re: Dax for Excel Index Match

satich989 — Thu, 10 Dec 2020 04:34:59 GMT

Thank you again Daniel.

Exactly the thing. I did a deep check and works very well, last thing that needs to be addressed is if a product has multiple status on the same day, for example product E is ordered and shipped on the same day, then the dax is breaking and shows both status instead of showing the latest status. Can we incude this extra condition in the dax to only show latest status? I see you created an index field in the status table, may be use that to determine the latest status?

Re: Dax for Excel Index Match

wdx223_Daniel — Thu, 10 Dec 2020 06:58:17 GMT

satich989 update code like this, it seems work

StatusToShow = VAR _date = MAX ( Dates[Date] ) VAR _maxdate = CALCULATE ( MAX ( 'Table'[Date] ), 'Dates'[Date] < _date ) VAR _status = CALCULATE ( CALCULATE ( MAX ( 'Status'[Index] ), 'Status'[Status] IN VALUES ( 'Table'[Status] ) ), 'Dates'[Date] = _maxdate, ALL ( 'Status' ) ) RETURN IF ( _date >= _maxdate && _date = EOMONTH ( _date, 0 ), CALCULATE ( MAX ( 'Table'[Status] ), 'Dates'[Date] = _maxdate, 'Status'[Index] = _status ) ) ------------------------------------------------------------ M = SUMX ( ALLSELECTED ( 'Table'[Product] ), VAR _date = MAX ( Dates[Date] ) VAR _maxdate = CALCULATE ( MAX ( 'Table'[Date] ), 'Dates'[Date] <= _date, ALL ( 'Status' ) ) VAR _status = CALCULATE ( CALCULATE ( MAX ( 'Status'[Index] ), 'Status'[Status] IN VALUES ( 'Table'[Status] ) ), 'Dates'[Date] = _maxdate, ALL ( 'Status' ) ) RETURN IF ( _date >= _maxdate && _date = EOMONTH ( _date, 0 ), CALCULATE ( DISTINCTCOUNT ( 'Table'[Product] ), 'Dates'[Date] = _maxdate, 'Status'[Index] = _status ) ) )

Re: Dax for Excel Index Match

Ashish_Mathur — Thu, 10 Dec 2020 12:12:34 GMT

Hi,

You may refer to my solution in this PBI file.

I tried to frame another solution with letting the source data stay intact and use only measures but i could not get a count of status wise and month wise poducts. I could only get the last status of each product. May be you or someone else can write a measure to get the count (from where the graph can be crested). You may download that PBI file from here.

Hope this helps.

Re: Dax for Excel Index Match

satich989 — Thu, 10 Dec 2020 22:14:38 GMT

Thank you Daniel

Re: Dax for Excel Index Match

satich989 — Thu, 10 Dec 2020 22:15:12 GMT

Thank you Ashish. The solution has most of what I need.

Re: Dax for Excel Index Match

Ashish_Mathur — Thu, 10 Dec 2020 23:06:25 GMT

You are welcome.