Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all
I have a report designed which pulls together data from 4 NHS Prescribing Data sites (England, Wales, Scotland, NI) into one appended table with a column for country (sample below).
Month | BNF Code | Quantity | Items | Cost | Item description | GP Practice | HBT | Country | FullPracRef | FullHBTRef | Quantity per item |
31/01/2019 | 0408020V0AAASAS | 3 | 1 | 137.28 | Midazolam maleate | - | - | England | England|- | England|- | 3 |
31/01/2019 | 1501041T0AAAAAA | 30 | 3 | 18.39 | Midazolam hydrochloride | N81053 | 01R00 | England | England|N81053 | England|01R00 | 10 |
31/01/2019 | 1501041T0AAAAAA | 30 | 3 | 18.39 | Midazolam hydrochloride | N81052 | 01C00 | England | England|N81052 | England|01C00 | 10 |
31/01/2019 | 1501041T0AAAAAA | 10 | 1 | 6.13 | Midazolam hydrochloride | N81028 | 02E00 | England | England|N81028 | England|02E00 | 10 |
31/01/2019 | 1501041T0AAAAAA | 20 | 1 | 12.26 | Midazolam hydrochloride | N81114 | 02E00 | England | England|N81114 | England|02E00 | 20 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
The Country field is connected to a countries table and the Month field is connected to a dates table which has a months field within it. BNF Code is connected to a products and brands table. I am plotting the data in a stacked area chart by brand (via BNF Code) as legend and month as axis.
The data for each country is released at different times, with Scotland data often being released after the following month's data for England is released (e.g. right now April England data is out, but March Scotland is still not released). Therefore my chart shows the last month as being a decrease simply because some data is not included. This is not always obvious since England is by far the largest dataset.
I want the data to refresh automatically, and always show all complete months, so I've written the following DAX measure that is intended to show only complete months and am using that as my values field in the chart:
//Return sum of quantity
Quantity (full months) = var MyValue = [Total quantity]
RETURN
IF(
//Check if all countries quantities are not zero (regardless of country/region filters applied) for that month
CALCULATE([Total quantity],
ALLEXCEPT('Prescribing data','Calendar'[Month]),
Countries[Country]="England")<>0 &&
CALCULATE([Total quantity],
ALLEXCEPT('Prescribing data','Calendar'[Month]),
Countries[Country]="Wales")<>0 &&
CALCULATE([Total quantity],
ALLEXCEPT('Prescribing data','Calendar'[Month]),
Countries[Country]="Scotland")<>0 &&
CALCULATE([Total quantity],
ALLEXCEPT('Prescribing data','Calendar'[Month]),
Countries[Country]="Northern Ireland")<>0,
//If all have data in that month, return value; otherwise blank
MyValue,
BLANK())
That works great for users who have access to all the data. However, I've been asked to set roles up for some users where they are only able to see subsets of data based on their sales regions, which cut across countries. So if User A has a region which only covers England, the DAX measure returns zeroes everywhere because as far as it's concerned there is no data in the other countries for any month.
Any ideas how I might be able to set this up differently please? I feel like I'm missing something obvious (other than adding a month filter that I manually update once the final data set is released and then republish, which I'd rather avoid if possible)...
Many thanks in advance!
Solved! Go to Solution.
Hi @BSawd ,
I did the following steps
1. Created a table CountryLastDate which has Country and LastDate of sales as columns.
This you can create from Sales Fact Table.
2. Created a Relationship between this and Geography on Country column
3. Created Measure
Thank you so much! This works perfectly, really appreciate the quick response. I'll mark this as the solution.
Only one nice to have: at the moment if I view the report as a user who only has access to certain countries, the data is still filtering to a date defined by the last availability of another country, even if that user doesn't have any data from that country. So for example, one user's regions only cover England and data is available to April for that country but unless I actively filter for England it's limiting data to Feb because that is the max date for Scotland. Not a huge issue, as it just means they need to click a slicer to get the extra month, but if it were an easy fix that would be great to have.
(apologies if this is duplicated, I tried to reply and it didn't seem to do anything)
Hi @BSawd
Here is the link to the pbix file in Google Drive
https://drive.google.com/file/d/1ZbhORF8eZreM8XdgdPA_bdyGvNIh1RqN/view?usp=sharing
Cheers
CheenuSing
Hi @BSawd ,
I did the following steps
1. Created a table CountryLastDate which has Country and LastDate of sales as columns.
This you can create from Sales Fact Table.
2. Created a Relationship between this and Geography on Country column
3. Created Measure