Measure to calculate status on date

[Adak]

Hi,

 I have some challange making a measure that returns status of the service. Data structure looks like this, there is Services table with every service ID as unique. Client can have few services. In Status table there is every date as fact then some service is "Not Available". The task is to get Table visualisation that would show for one what Services Clients have and what was the status of the service for selected Date from Date table, if Status is not "Not Available" it should return "Available".   I suppose a measure needs to be used, because to have Status for every Service for Every date would be billions of rows in real scenario, so I need a measure that would check if there is "Not Avalable" status in Status table for selected date, and return it otherwise it should return "Available".  

 

Would be gratefull for help.

 Here is a link to demo pbix

Regards,

Adak

[Greg_Deckler]

Can you post sample/example data that can be copied and pasted? 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

[Adak]

Sure. I've attached pbix for that reason, but yes example for copying is nice:

Main table is Services:

ServiceID	ServiceName	ClientID
1	Football	1
2	Basketball	1
3	Football	2
4	Swimming	3
5	Basketball	4

Status table has dates when some service was unavailable:

ServiceID	Status	Date
1	Not Available	2018-01-01
1	Not Available	2018-01-02
1	Not Available	2018-01-03
2	Not Available	2018-02-01
2	Not Available	2018-02-02
3	Not Available	2018-01-05

Clients table is simple:

ID	Name
1	John
2	Peter
3	Mary
4	Jo

What I like to get as result in report is a table that if date 2018-01-02 is selected it would look like this:

Name	ServiceID	ServiceName	Status
John	1	Football	Not Available
John	2	Basketball	Available
Peter	3	Football	Available
Mary	4	Swimming	Available
Jo	5	Basketball	Available

[v-yuta-msft · Accepted solution]

Hi Adak,

To achieve your requirement, follw steps below:

1. Merge table 'Clients' with table 'Services' as 'Merge1', then merge 'Merge1' with table 'Status' as Merge2,.

 

2. Create a calendar table with only one column in query editor using M code like below:

let
    Calendar = List.Dates(#date(2018, 1, 1), 100, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Calendar, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Calendar"}})
in
    #"Renamed Columns"

 

3. Merge 'Merge2' with 'Calendar' using right outer join as 'Merge3'

 

The result is like below and PBIX for your reference: https://www.dropbox.com/s/hfbzlyrkb8ttgyl/bi%20status%20challange.pbix?dl=0

 

Best Regards,

Jimmy Tao

[Adak]

Thanks Jimmy for this idea and solution,

 Totally understand what you did, and it looks like it would work in small scale. However in real case such table is generating quite many records and that becomes a problem to store all this data, thats why I'm thinking that DAX could be a better approach than M. Also I need to show a list of services for a date like:

Basketball Available

Football Avalable

Swimming Not Available

 Using a table that means I have to have status for every service, every day if I have 50 000 services and want to have history for 3 years that would be 54 750 000 rows.

Any idea how to overcome this problem?

Regards,

Adak

[v-yuta-msft]

 Hi Adak,

The time-complexity of M code in power query is better than DAX when you are implementing function on large amount of records. In addtion, I would recommend you to use 64-bit power bi version so that there's no memory limitation when big data comes. And also, if you still fill it's to slow in power bi, you can do the same operation in database and then import the result table to power bi.

Regards,

Jimmy Tao