How to determinate unactive user in a specific period, usining DAX/PowerQuery/CalcultedTable?

[SeymaKalay]

I have 1 fact and dim table as below. Expected DAX/PowerQuery/CalcultedTable should answer how many & which users were active & unactive in 6/28/2025.
Fact Table
ID USER     ACTIVE USER ID     DATE         IF.ACTIVE
-                   -                           6/28/2025    0 (if the user is not active, dont have any recorded value)
-                   -                           6/28/2025    0
-                   -                           6/28/2025    0
123456     123456                    6/28/2025    1

Dim User Table
ID USER
123456
234567
345678
456789

Expected Output
ID USER      is.Active    Date
123456       1               6/28/2025
234567       0               6/28/2025
345678       0               6/28/2025 
456789       0               6/28/2025

[danextian]

Hi @SeymaKalay 

The simplest approach is to create a calculated column in Users table

Is.Active = 
LOOKUPVALUE ( FactTable[IF.ACTIVE], FactTable[ID USER], Users[ID USER] ) + 0

Date = MAX ( FactTable[DATE] )

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar table with a relationship (Many to One and Single) from the Date column of the dact table to t he Date column of the Calendar table

2. Create a slicer from the Date column of the Calendar table and select any one date

3. From the Dim table, drag ID user to the Table visual

4. Write this measure

Measure = countrows(Data)

Measure1 = if([Measure]=0,0,1)

Hope this helps.

[DataNinja777 · Accepted solution]

Hi @SeymaKalay ,

You can determine active and inactive users for a specific date like June 28, 2025, by comparing your master list of users against the activity logs. This can be accomplished in Power BI using DAX for dynamic measures, a DAX calculated table for a static snapshot, or within the Power Query editor to pre-process the data. Each method has its advantages depending on your specific needs for interactivity and performance.

The most flexible and recommended approach is to use a DAX measure. This method calculates the activity status dynamically, allowing you to use slicers to change the target date without altering the code. It works by creating a calculation that is evaluated within the context of a visual. To implement this, you would first create a measure that checks for a user's activity on the specified date.

is.Active =
VAR SelectedDate = DATE(2025, 6, 28)
VAR ActivityCount =
    CALCULATE(
        COUNTROWS('Fact Table'),
        'Fact Table'[DATE] = SelectedDate
    )
RETURN
    IF(ActivityCount > 0, 1, 0)

To display the result, you would place the ID USER column from your Dim User Table into a table visual, and then add the [is.Active] measure. The relationship between your dimension and fact tables ensures the calculation is performed correctly for each user. To complete the output with a date column, you can create another simple measure, Selected Date = DATE(2025, 6, 28), and add it to the visual.

Alternatively, if you require a physical table containing this information within your data model, you can use DAX to create a calculated table. This approach generates a static table based on the data at the time of the last refresh. It is useful for creating a fixed dataset that can be used in other calculations. You would create this new table by using a formula that iterates through every user in the dimension table and checks if they have a corresponding entry in the fact table for the target date.

User Activity on 2025-06-28 =
ADDCOLUMNS (
    'Dim User Table',
    "is.Active",
    VAR CurrentUser = 'Dim User Table'[ID USER]
    VAR ActivityOnDate =
        CALCULATE (
            COUNTROWS ( 'Fact Table' ),
            'Fact Table'[USER ID] = CurrentUser,
            'Fact Table'[DATE] = DATE ( 2025, 6, 28 )
        )
    RETURN
        IF ( ActivityOnDate > 0, 1, 0 ),
    "Date", DATE ( 2025, 6, 28 )
)

A third method is to perform this entire transformation within the Power Query Editor. This is a highly performant option as the logic is applied to the data during the refresh process, before it is loaded into the model. The process involves merging the Dim User Table with a filtered version of the Fact Table that contains only the active users for June 28, 2025. By using a Left Outer join, you keep all users from the dimension table and get null for those who were not active. You can then add a conditional column to convert these null and non-null values into a 0 or 1 flag for the is.Active status.

// M-Code for adding the conditional column in Power Query
Table.AddColumn(#"Previous Step", "is.Active", each if [MergedColumnName] = null then 0 else 1)

In summary, your choice of method depends on your goal. Use DAX measures for dynamic and interactive reports where users can select dates. Opt for a DAX calculated table when you need a fixed, physical snapshot of user activity for a specific date. Use Power Query when you want to pre-process the data for performance reasons, embedding the transformation logic into the data refresh steps.

Best regards,

[speedramps]

Try this ...

Add a relationship

add measure

measure = 
var temptable = 
ADDCOLUMNS(
    'Dim User',
    "Active",
    SUM(Facts[ IF.ACTIVE]) + 0
)
RETURN
IF(HASONEVALUE('Dim User'[ID USER]),
SUMX(temptable,[Active]),
SUM(Facts[ IF.ACTIVE])
)

Build your report

How it works ...

• ADDCOLUMNS build a temp dim user table with an extra column called "Active"
• The IF HASONEVALUE determins if the cursors is on Usaer row or the Total row
• SUMX gets the total for the ID from the temp table
• SUM gets the  total for all the IDs from the temp table

Please clik thumbs up because I have tried to help you.

Then click accept solution if it works.

(See the screen prints. It clearly does work as you requested)

Thank you