Displaying data in a Matrix - Showing last available data, as well as rows with no data

[Anonymous]

Hi,

I've been working on this dashboard for a while (my very first one), but I'm stuck at this point:

I have a table with data, that contains a string of |location + business unit + sub-location| that relates the data to another table with more information about the location (i.e country etc).

For some reason there are 2 things that i can not figure out:

1. Iis there a way to display all of the locations, even if there are no KPI's for it in the data table ?

(i tried adding empty entries, but then they only work for the month that they are filled in for, but would be a solution if i can figure out 2)

2. Does anyone know how i can only dispay the last entry for each location ? at the moment it filters for the last month as standard setting, but it would be a lot better if it would also show the lats entry for the locations that have not submitted the KPI's yet...

I hope that someone has an idea how to solve this, I have looked through Google for quite a while now, but nothing I can find seems to work for me..

Please let me know if you have any suggestions or questions that might help find one 😄

Best,
Julius

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Anonymous]

Hi @Icey ,

Unfortuntaley I haven't had a chance to test everything out but I will either accept one as solution or post the one I found (since I also asked around in the Power BI reddit community, as i didn't have access here for a while due to a request taking a while to process..)

But thanks a lot for your quick replies @Anonymous  & @Icey !! 😄

Best, 

Julius

[Icey]

Hi @Anonymous ,

I create an example. Please check if this is what you want:

1. Two tables.

Table 1:

Location	SubLocation
A	A1
A	A2
A	A3
B	B1
B	B2
B	B3

Table 2:

SubLocation	Year	Month	KPI
A1	2019	Oct	1
A1	2019	Nov	2
A1	2019	Dec	3
A1	2020	Jan	4
A2	2019	Oct	5
A2	2019	Nov	6
A3	2019	Oct	7
A3	2019	Nov	8
A3	2019	Dec	9
B1	2020	Jan	10
B2	2019	Nov	11
B2	2019	Dec	12
B3	2019	Oct	13
B3	2019	Nov	14
B3	2019	Dec	15
B3	2020	Jan	16

2. Create relationship.

3. Create a measure.

Measure = IF ( SUM ( 'Table 2'[KPI] ) = 0, "NULL", SUM ( 'Table 2'[KPI] ) )

For details, please check the attached PBIX file.

Best Regards,

Icey

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

[Anonymous]

I think this article by Alberto Ferrari might be helpful regarding your second question:

https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

It is neither short nor an easy read as the topic is rather complex, but it comes with detailed explanations and code. If you take your time, it might give you exactly what you want.

Regards,

Mo