Filtering by account type and date

[bdpr_95]

Hello everyone,

I have 3 datasets (fac_products, fac_services and dim_date).

The connection between the fac_tables is the account_id.

Find below the structure of which table:

The first step is to create a new table, in which for the products_table if I only have 1 active product, then have an "account_type" column that tells me the name of the product, when both are active have "dual" and when it returns to having only 1 active product the name of the product again.
Below is an example of what I need.

The last step is to have a Report that would allow me to filter by date and by account type.
For example, if I filtered for January 2022 and in the account type for Mono - Product A, 1 active service would appear in the donut chart (Service A).
If I filtered for July 2022 and for Dual (in the account type) 2 services would appear - Service A and Service B.
Below is the layout of what I need:

Does anyone know how to do this?

Is it possible to create a table to give me the account type? What's the best way? Power Query or DAX?

Thank you!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[bdpr_95]

Hello @lbendlin find below the data and the expected output:

- Data:

Products Table

acct_id	acct_start_date	acct_end_date	product_id	product_name	product_start_date	product_end_date
1	01/01/2022	31/12/2022	5	Product A	01/01/2022	31/07/2022
1	01/01/2022	31/12/2022	36	Product B	01/06/2022	31/12/2022

Services Table

acct_id	acct_start_date	acct_end_date	service_id	service_name	service_start_date	service_end_date
1	01/01/2022	31/12/2022	45	Service A	01/01/2022	31/12/2022
1	01/01/2022	31/12/2022	86	Service B	01/06/2022	31/12/2022

- Expected Output:

acct_id	acct_start_date	acct_end_date	account_type	start_date	end_date
1	01/01/2022	31/12/2022	Mono Product A	01/01/2022	31/05/2022
1	01/01/2022	31/12/2022	Dual	01/06/2022	31/07/2022
1	01/01/2022	31/12/2022	Mono Product B	01/08/2022	31/12/2022

Thank you!

[lbendlin]

Not sure what account types have to do with this but here is an alternative proposal on how to structure your data and how to visualize it.

[bdpr_95]

@lbendlin thanks for the quick answer but that is not what I need.

The objective of this is for the business to understand for a given date how many services it has per account type.
Example:
Business Unit filters for July 2022 and wants to know how many services we have for "Dual" accounts.
Then I go to the date filter and put July 2022 and I go to the account type filter and put "Dual". What the donut chart is supposed to show me are 2 services divided by typology (in this case 1 Service A and another Service B).
If I changed the filter to January 2022 and set the filter to "Dual", I should no longer see any service for that account type since on that date the account type was "Product A".

Did you understand the logic?

[lbendlin]

You keep mentioning Account type but I don't see that in the sample data.  Please provide sample data that fully covers your issue.

[bdpr_95]

Watch the expected output (after the account end date column).

[v-yiruan-msft]

Hi @bdpr_95 ,

If I understand correctly, you would like to get the account type first base on the active products. Then you want to get the service info by the filtered account type and selected date range. I'm not clear about the backend logic about getting the account type and service info, could you please explain it with more details(some specific examples, screenshot etc.)? Thank  you.

Best Regards

[bdpr_95]

Hello, @v-yiruan-msft your logic is correct.

Every day the products data and the services data are updated. Basically, the business logic is: I acquire products (i can have multiple products but only 2 active the rest have to be in "inactive status") and I can subscribe services. The service is like an insurance for the product that I acquire and I can have more than one service per account.

I don't know if what I need is possible to develop in Power Bi, but what I want is two filters, one is the date and the other is the account type.

I tried to develop an example, in this pbix I have the products table, services table and I created a new table (manually) that gives me the account type.  

The example has 3 accounts (imagine we are in January 2023) :

- 1 account with 2 inactive products and 2 inactive services;

- 1 account with 1 active product and 1 active service;

- 1 account with 1 inactive product without services.

If I filter by July 2022 and select "All" for the Account Type filter, what I pretend to watch is a donut chart with 1 service A and 2 service B.

If I filter by "Dual" (and the filter date remains unchanged) I want to watch 1 service A and 1 service B.

To conclude, what I need to know is if it is possible to make this account type table (or another approach that allows me to have the account type) and then if it is possible to have this filter and that it can relate to the metric that I created of "# active services" (in the example.pbix that I develop it is not related well because despite the connection between tables there is no date factor which causes the results to be incorrect).

products_table code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjIyDH2FDf0AjGMQXigKL8lNLkEgVHLCoNzCGcWB1CJhmbIRnlBFVqhkUpyCgjqLwxTB6ITLC5BaEApM0Yw1gDfUMDGMfCFK8LEEpjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [acct_id = _t, acct_start_date = _t, acct_end_date = _t, product_id = _t, product_name = _t, product_start_date = _t, product_end_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"acct_id", Int64.Type}, {"acct_start_date", type date}, {"acct_end_date", type date}, {"product_id", Int64.Type}, {"product_name", type text}, {"product_start_date", type date}, {"product_end_date", type date}})
in
#"Changed Type"

services_table code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjIyDH2FDf0AjGMTEFEsGpRWWZyakKjniUxuoQMsrCDMkoJ6hSMxxGGUHljWHyQGRpiWaAoSmygthYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [acct_id = _t, acct_start_date = _t, acct_end_date = _t, service_id = _t, service_name = _t, service_start_date = _t, service_end_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"acct_id", Int64.Type}, {"acct_start_date", type date}, {"acct_end_date", type date}, {"service_id", Int64.Type}, {"service_name", type text}, {"service_start_date", type date}, {"service_end_date", type date}})
in
#"Changed Type"

account_type_code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjIyDH2FDf0AjG8c3Py1fQVQgoyk8pTS5RcMSi2MAUwonVIWSYS2liDlSBGbIB5sQagOYaJ6hiCyyKQYYZQeWNYfK4PYRQAtJojOFKA31DAwKuwKY4NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [acct_id = _t, acct_start_date = _t, acct_end_date = _t, account_type = _t, start_date = _t, end_date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"acct_id", Int64.Type}, {"acct_start_date", type date}, {"acct_end_date", type date}, {"account_type", type text}, {"start_date", type date}, {"end_date", type date}})
in
#"Changed Type"

#active_services measure:

# active_services =

VAR Data =

    MAX(Dim_Date[Date])

RETURN

CALCULATE(

    DISTINCTCOUNT(fact_services_table[service_id]),

    ALL(dim_Date[Date]),

    fact_services_table[service_start_date] <> BLANK(),

    fact_services_table[service_start_date] <= Data,

        OR(

            fact_services_table[service_end_date] > Data,

            fact_services_table[service_end_date] = BLANK()

        )

)

dim_date

Relation Model

Report: