All items from one SharePoint list and a calculation from another related SharePoint list.

[rheitrich]

This is difficult to explain.

I have two SharePoint lists as data sources. One is a list of assets (Division/Location, AssetID, Asset Description). The second list is for asset inspections (AssetID, Inspection Date, Description).

I want a simple matrix visualization that shows all of the assets (rows) from the first list grouped by the division, whether an inspection has been performed or not. Columns for the Month-Year across the top, then as inspections are completed, the field value turns from red to green. I have everything working EXCEPT it only shows assets that have an inspection sometime during the date range specified (rolling 12 months). I want it to ALWAYS show all assets from the asset list and then change the color of the cell depending on whether the inspection has been done or not during the related month-year.

Any help would be appreciated. (The two lists are joined on assetID and that is it).

[v-jayw-msft · Accepted solution]

Hi @rheitrich ,

Check the document about show-items-no-data feature.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data 

If its' not what you want, please share more details like the fact table and the expected out put.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rheitrich]

That is embarrassingly simple considering how much time i spent on it. Thanks so much for your response and your help.

[amitchandak]

@rheitrich ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

I am assuming you already have data.  You can following with a date calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD(Table[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))

Create a color measure using these. example

color =
switch ( true(),
FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen",
FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen",
/// Add more conditions
"red"
)

In the conditional formatting, you can choose a measure, after selecting a "Field" Option

Refer

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.