Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BSawd
Frequent Visitor

Return months where data is complete, with access controls

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). 

MonthBNF CodeQuantityItemsCostItem descriptionGP PracticeHBTCountryFullPracRefFullHBTRefQuantity per item
31/01/20190408020V0AAASAS31137.28Midazolam maleate--EnglandEngland|-England|-3
31/01/20191501041T0AAAAAA30318.39Midazolam hydrochlorideN8105301R00EnglandEngland|N81053England|01R0010
31/01/20191501041T0AAAAAA30318.39Midazolam hydrochlorideN8105201C00EnglandEngland|N81052England|01C0010
31/01/20191501041T0AAAAAA1016.13Midazolam hydrochlorideN8102802E00EnglandEngland|N81028England|02E0010
31/01/20191501041T0AAAAAA20112.26Midazolam hydrochlorideN8111402E00EnglandEngland|N81114England|02E0020
....................................

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!

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

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

   

RevnueBYCountry =
VAR _SelMinDate =
MINX ( ALLSELECTED ( Geography ), RELATED ( CountryLastDate[LAstDate] ) )
RETURN
CALCULATE (
SUM ( Sales[Revenue] ),
FILTER ( ( 'Date' ), 'Date'[Date] <= _SelMinDate )
)
 
4.  Created a matrix  visual to display the measure.
 
I am unable to add the pbix file here. I will try to add as apersonal message. Take a look.
 
If it solves your problem please accpet this as a solution and also give kudos.
 
Please share your pbix if you need further help.
 
Cheers
 
CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
BSawd
Frequent Visitor

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)

CheenuSing
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
CheenuSing
Community Champion
Community Champion

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

   

RevnueBYCountry =
VAR _SelMinDate =
MINX ( ALLSELECTED ( Geography ), RELATED ( CountryLastDate[LAstDate] ) )
RETURN
CALCULATE (
SUM ( Sales[Revenue] ),
FILTER ( ( 'Date' ), 'Date'[Date] <= _SelMinDate )
)
 
4.  Created a matrix  visual to display the measure.
 
I am unable to add the pbix file here. I will try to add as apersonal message. Take a look.
 
If it solves your problem please accpet this as a solution and also give kudos.
 
Please share your pbix if you need further help.
 
Cheers
 
CheenuSing

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors