Forum Discussion

ivan15's avatar
ivan15
New Member
7 years ago

need help with a measure

Hi all,

 

i am trying to build a dashboard with a item bidding streaming dataset.  The dataset are similar to the following:

 

itembidnametimestamp
watch10john7/4/2019 0:00
ipad 300ian7/4/2019 18:00
ipad 300peter7/4/2019 19:00
ipad 240john7/4/2019 19:00

 

I am trying to build a table to display highest bid by name and item like the following:

ItemHighest bidname
ipad300ian
watch10john

 

I created a measure to get highest bid per item with 

HighestBidPeritem =
MAXX(
    KEEPFILTERS(VALUES('RealTimeData'[item])),
    CALCULATE(MAX('RealTimeData'[bid]))
)
I created a measure to get the name with highest bid
HighestBidPerItem_employee = CALCULATE (
    SELECTEDVALUE ( RealTimeData[employee], "*** multiple bids ***" ),
    FILTER ( ALL ( RealTimeData[bid]), RealTimeData[bid] = MAX (RealTimeData[bid]) )
)
The issue is SELECTEDVALUE return blank if there are 2 bids with the same amount.   Then I tried to add a filter for min timestamp.  But got the same result.
HighestBidPerRoom_employee = CALCULATE (
    SELECTEDVALUE( RealTimeData[employee] ),
    FILTER ( RealTimeData, RealTimeData[bid] = max(RealTimeData[bid]) ),
    FILTER ( RealTimeData, RealTimeData[timestamp] = min(RealTimeData[timestamp]) )
)
Really appreciate for any help.
 
 
 
 
 
 

 

 

 

Need Help

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi ivan15 ,

     

    I created a model using your first and third measure and the final result is below:

     

    So I don't see any issue with your measure only thing I would change  is how you make your filter function:

     

    HighestBidPerRoom_employee =
CALCULATE (
    SELECTEDVALUE ( RealTimeData[name] );
    FILTER (
        ALLSELECTED ( RealTimeData[bid]; RealTimeData[timestamp] );
        RealTimeData[bid] = MAX ( RealTimeData[bid] )
            && RealTimeData[timestamp] = MIN ( RealTimeData[timestamp] )
    )
)

    Using the ALLSELECTED function and having only the columns that you want to filter will increase performance. When I first started working with DAX a great guru (Alberto Ferrari) replied to a post I was answering and referred that if you use a filter function on a full table if you have a lot of data your performance will suffer because of the interactions that will cover all rows in the table, selecting only the columns you need the number of interactions decrease.

     

    What is the version you are using of PBI?

     

    Regards,

    MFelix

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, ivan15 

    I have test your formula on my side, it works well.

    And I think if there are [name] with the same max([bid]) and min(RealTimeData[timestamp]) leads the problem.

    Could you share your sample pbix file for us have a test?

     

    Best Regards,

    Lin