Filter on the right row for each user based on the timeframe
Hi guys, I have a mix of data coming from Google Analytics for web traffic which I want to join in with our own DWH to be able to get the best possible insights. So we have the following tables: 1 - Google Analytics - Traffic - It contains traffic on our website. The key here is user_id which can be joined with our DWH to find out more about this user/customer. 2 - Contact - SQL - It contains all our company customers/members in DWH. The key here is user_id to join with Google Analytics table. The table only contains there last status change. 3 - Contact History - SQL - This table contains historical changes for each customer/member in DWH. A customer can go from being a customer to becoming a member and later on go from being a member back to customer. For each change, there is a new row with a dbt_valid_from and dbt_valid_to date. What I am looking for: I want to count the number of distinct user_id in Google Analytics - Traffic for the selected dates in "Date" table and I would like to filter on the "active" row at that time. I want to make a line chart from Jan 2024 to September 2024. In this chart I would like to get the customer_type status for each user for each month. The dbt_valid_from and dbt_valid_to columns are the ones to tell when a row is valid or not. The slicer date must be between dbt_valid_from or dbt_valid_to date to select the correct. For current active status the dbt_valid_to can also be NULL as the rows is not expired yet. I am also attaching a shot from my model. Thanks for all help in advance. This is the DAX I have tried so far but it is not giving me the correct row: Active User Count = VAR SelectedDate = SELECTEDVALUE('Date'[Date]) RETURN CALCULATE( DISTINCTCOUNT('Google Analytics - Traffic'[user_id]), FILTER( 'Contact History', ('Contact History'[dbt_valid_from] <= SelectedDate) && ( ISBLANK('Contact History'[dbt_valid_to]) || ('Contact History'[dbt_valid_to] >= SelectedDate) ) ) ) What I am looking to visualise: I am basically looking for a visual like this. If a user is customer in January and turns member in July, I want it to be registered as a customer in January and member in July. Thanks in advance for all help.
Writing measure to show multiple visuals on the same chart with one slicer
Hello, I am sorry to keep posting looking for a solution on this. I am trying to write a measure. I have tried multiple methods but cant get anything to work. I would assume having two different measures on the same visual would work. I have two tables (Table1 and Table2). My goal is to use one slicer and show multiple visuals from the same table on the same chart. For example, if a month is selected, I want to show data (filtered with different conditions) for that month, and the month most recent to the selected month. Column 'Table1'[Month] is linked to Column 'Table2'[Month] with a one to many relationship (Table2-one, Table1-many). Then I have a slicer that uses column 'Table2'[Month]. The visual for my current month works, but now I am trying the add the data to the same visual for its previous month. For the previous month, If the user selects a month with the slicer, I want to save the [Previous Month] associated in Table2 column as text. So if 24-Jun is selected, I want to save "24-May" as text. Then I want to filter and count the number of rows in Table1 where [Month End] = the saved text & Type = "Task". So in this example, I would count the number of rows in Table1 where Table1[Month End] = "24-May" & Type = "Task", and this would return 1. Table1 ID Status Date Rank Previous Status Month Type Group Previous Month A Normal 3 No previous 24-Mar Task <0 No previous B Normal 3 No previous 24-Mar Task 1 to 5 No previous C Not Normal 3 No previous 24-Mar Task 1 to 5 No previous D Not Normal 3 No Previous 24-Mar Not Task 11 to 15 No previous A Not Normal 2 Normal 24-May Task 6 to 10 24-Mar B Normal 2 Normal 24-May Not Task 6 to 10 24-Mar C Normal 2 Not Normal 24-May Not Task 6 to 10 24-Mar D Not Normal 2 Not Normal 24-May Task 6 to 10 24-Mar A Not Normal 1 Not Normal 24-Jun Task 1 to 5 24-May B Not Normal 1 Normal 24-Jun Task <0 24-May D Normal 1 Normal 24-Jun Task <0 24-May E Normal 1 24-Jun Task 1 to 5 24-May Table2 Date Rank Month Next Date Rank Previous Month 3 24-Mar 4 2 24-May 3 24-Mar 1 24-Jun 2 24-May Sample of visual I'm trying to create: Measure that doesn't work for previous month: (I keep getting blank when selecting a current month) VAR SelectedMonth = SELECTEDVALUE('Table2'[Month]) VAR PreviousMonth1 = CALCULATE( MAX('Table2'[Previous Month]), FILTER( ALL('Table2'), 'Table2'[Month] = SelectedMonth ) ) VAR CountRows1 = CALCULATETABLE( 'Table1', FILTER( ALLSELECTED('Table1'), 'Table1'[Month] = PreviousMonth1 && 'Table1'[Type] = "Task" ) ) RETURN COUNTROWS(CountRows1) Measure works for selected month: VAR SelectedMonth = CALCULATE( MAX('Table1'[Date Rank]), FILTER( ALL('Table1'), 'Table1'[Month] = SELECTEDVALUE('Table2'[Month]) ) ) VAR _tablecalculation = CALCULATETABLE( 'Table1', ALLSELECTED('Table2'), 'Table1'[Date Rank] = SelectedMonth, 'Table1'[Status] = "Not Normal", 'Table1'[Previous Status] = "Not Normal", 'Table1'[Type] = "Task" ) RETURN COUNTROWS(_tablecalculation) Any help in the right direction is definetly appreciated!
Using ALL function with relationship
Hi, I am trying to calculate how many question completed by user. For example in this month there are 10 people and 3 of the answered question-1 and 5 of the answered question-2 so question-1 Rates should be 0.3 and question-2: 0.5 My formula like that IF( HASONEVALUE(DateSlicer[period]) && SELECTVALUE(DateSlicer[period]) = "month"), CALCULATE(CALCULATE(DISTINCTCOUNT(Table[userID]),Table[Category] = "a", Table[Questions] =MAX(Table[Questions])/CALCULATE( DISTINCTCOUNT(Table[userID]), Table[Category] = "a", ALL(Table),USERELATIONSHIP(DateSlicer[DateFormats], DateTable[year])) But in this case I am using ALL functional but it smashing to USERELATIONSHIP Function. So I can not calculate right. How can do it with using this USERELATIONSHIP function?
Blank Values when multiple filters are applied
Hello - I have a simple dataset that looks like this: Category Sub-Category Class Value XX Value YY AAA X1 A 1000 2000 AAA X2 A 400 2500 AAA X3 B 2000 300 BBB X2 A 5000 1000 CCC X1 B 150 1200 CCC X2 B 350 10000 CCC X3 A 300 800 Then I have a summary table that looks like this: Category Value A (=Value XX/2) Value B(=Value YY/2) AAA 1700 2400 BBB 2500 500 CCC 400 6000 Then I have two filters, one for Value A (Filter A) and one for Value B (Filter B), that are the result of the concatenation of: Filter A = "Class" + "Sub-Category" (ie. A-X1, A-X2, B-X3,...) Filter B = "Class_duplicate" + "Sub-Category_duplicate" (ie. A-X1, A-X2, B-X3,...) --> I created a duplicate field for both "Class" and "Sub-Category" to avoid interference between the two filters One of my goals was to apply Filter A only to Value A (with Value B not impacted by any selection) and Filter B to apply only to Value B (with Value A not impacted by any selection). I was able to accomplish this with the following measures: Value A = CALCULATE( DIVIDE(Value XX,2), ALL(Table_name, Class, Sub-Category) ) Value B = CALCULATE( DIVIDE(Value YY,2), ALL(Table_name, Class_duplicate, Sub-Category_duplicate) ) This works only if: I apply one Filter at a time OR if the selected combination of "Class"+"Sub-Category" from both filters is the same But considering that my summary table is at the "Category" level, while my filters are at the "Class"+"Sub-Category" level, the PROBLEM is if: I make a selection from both filters AND the combination of "Class"+"Sub-Category" is different between the two filters, which will return blank values in my summary table for both Value A and Value B. I tried combining different functions inside the measures Value A and Value B, but with no success. I hope I made it clear to understand, any recommendation/hint will be greatly appreciated. Thanks! Davide
