How do I determine inactive users in a specific period, using DAX/PowerQuery/CalculatedTable?

Question

I have a fact and a dim table as shown below. Expected Table/DaxCode/Powerquery should answer how many & which users were active & inactive in 6/28/2025.

Fact table

    ID USER    ACTIVE USER ID    DATE        IF.ACTIVE
     -             -             6/28/2025      0 (if the user is not active, don't have any recorded value)
     -             -             6/28/2025      0
     -             -             6/28/2025      0
    123456      123456           6/28/2025      1

Dim User table

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

Leyth · Accepted Answer · 2025-06-28 22:24:01Z

Here is one strategy you can follow :

Create a new DimDate table containing unique date values of the FactTable. To do so you can create the following DAX table:

DimDate = SUMMARIZE('FactTable','FactTable'[DATE])

Now we are going to cross join your DimTable and this new DimDate table, there are many ways to do this, here is one : Create a new constant column (equal to 1 for example) in both DimTable and DimDate let's name these columns Cross. Here is an ultra simple DAX column code:

Cross = 1

Now you can go to model tab and definie an active *<=>* relationship between DimTable table and DimDate table linking the 2 Cross columns. If you now create a table visual and put ID USER column of DimTable and Date column of DimDate table you will see the cross join.
We now need to check if we had a 1 in IF.ACTIVE column for the given Date and the given ID USER in FactTable to do so you can create the following DAX measure:

is.Active = IF(
                ISBLANK(CALCULATE(MAX('FactTable'[IF.ACTIVE])
                          ,'FactTable'[DATE]=MAX('DimDate'[Date])
                          ,'FactTable'[ID USER]=MAX('DimTable'[ID USER])
                            )
                       )
              ,0
              ,1
              )

You can now create a table visual and add the following columns:
- ID USER column of DimTable table
- Date column of DimDate table
- is.Active measure

You should get the desired result.

Collectives™ on Stack Overflow

How do I determine inactive users in a specific period, using DAX/PowerQuery/CalculatedTable?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related