Forum Discussion

alex1888's avatar
alex1888
New Member
5 years ago
Solved

Build an asset availability report

Hello,  I have the following requirement that I'm not sure can be realized using Power bi. My data source is dynamics 365, And involved in the data structure 2 relevant entities Assets and Allocatio...
General Comment
Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  alex1888 ,

     

    Create a measure as below:

    Measure = 
var _mindate=CALCULATE(MIN('calendar table'[Date]),ALLSELECTED('calendar table'))
var _maxdate=CALCULATE(MAX('calendar table'[Date]),ALLSELECTED('calendar table'))
var _tab=CALCULATETABLE(VALUES('Allocation to assets'[Assets ID]),FILTER(ALL('Allocation to assets'),'Allocation to assets'[END DATE]<=_mindate||'Allocation to assets'[START DATE]>=_maxdate))
VAR _names=CALCULATETABLE(VALUES(Assets[ASSET NAME]),FILTER(ALL(Assets),'Assets'[Assets ID] in _tab))
Return
CONCATENATEX(_names,'Assets'[ASSET NAME],",")

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi  alex1888 ,

 

Create a measure as below:

Measure = 
var _mindate=CALCULATE(MIN('calendar table'[Date]),ALLSELECTED('calendar table'))
var _maxdate=CALCULATE(MAX('calendar table'[Date]),ALLSELECTED('calendar table'))
var _tab=CALCULATETABLE(VALUES('Allocation to assets'[Assets ID]),FILTER(ALL('Allocation to assets'),'Allocation to assets'[END DATE]<=_mindate||'Allocation to assets'[START DATE]>=_maxdate))
VAR _names=CALCULATETABLE(VALUES(Assets[ASSET NAME]),FILTER(ALL(Assets),'Assets'[Assets ID] in _tab))
Return
CONCATENATEX(_names,'Assets'[ASSET NAME],",")

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!