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
satich989
Frequent Visitor

Dax for Excel Index Match

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

ProductOrderedShippedDeliveredReturnedled
A1-Apr-202-Apr-201-Jan-001-Jan-001-Jan-00
B3-Jan-206-Jan-2016-Mar-2017-Mar-201-Jan-00
C6-Apr-201-Jan-001-Jan-001-Jan-0012-Aug-20
D3-Jan-201-Jan-001-Jan-001-Jan-006-Jan-20
E3-Feb-206-Feb-2016-Apr-2017-Aug-201-Jan-00


Product.png

 

4 ACCEPTED SOLUTIONS

wdx223_Daniel_0-1607490788390.pngyou need a real date table with all dates

 

View solution in original post

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

View solution in original post

Hi,

You may refer to my solution in this PBI file.

Untitled.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

15 REPLIES 15
wdx223_Daniel
Super User
Super User

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

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 

satich989_0-1607438791559.png

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?

 

satich989_0-1607442051269.png

 

 

 

@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'))
)

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.

satich989_3-1607478805292.png

 

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'.

 

satich989_4-1607478986629.png

 

wdx223_Daniel_0-1607490788390.pngyou need a real date table with all dates

 

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

 

satich989_3-1607534112029.png

 

 

When not filtered for a product, it shows total

 

satich989_2-1607534089968.png

 

 

 

 

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?

 

satich989_0-1607574638176.png

 

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

Thank you Daniel

Hi,

What is the final result you want?

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

Please clarify.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Status Count.png

Hi,

You may refer to my solution in this PBI file.

Untitled.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Top Solution Authors