Forum Discussion
All items from one SharePoint list and a calculation from another related SharePoint list.
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).
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.
4 Replies
- Greg_Deckler
Community Champion
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. - amitchandak
Super User
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-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesPlease Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos. 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.
That is embarrassingly simple considering how much time i spent on it. Thanks so much for your response and your help.
