Forum Discussion

conniedevina's avatar
conniedevina
Helper I
10 months ago
Solved

Count the total ID based on date selection in multiple date column

Hi, 

 

I am having this kind of fact_table

IDcategory_acategory_bcategoryA_datecategoryB_date
12345healthynon-healthy2024-01-012024-03-01
12333healthysick2024-03-012024-01-01
12333sickhealthy2024-05-012024-03-01
12343sickhealthy2024-05-012024-06-01
12344very healthynon-healthy2024-03-012024-05-01

 

then I will have a date_table

Date

2024-01-01

2024-01-02

2024-01-03

 

and so on for all dates, to use this date_table as slicer

 

currently I did this data model, based on previous thread I created here

 

I will use date_table as slicer, and I want to have total distinct ID

example
date slicer selected 2024-01-01 to 2024-03-01

the ID selected will be

12345
12333
12344

 

because all those 3 categoryA_date and categoryB_date are in the selection in dateslicer.

 

Appreciate any help!

 

Need Help
  • rohit1991's avatar
    rohit1991
    10 months ago

    Hi conniedevina 

     

    You can get the total distinct ID across both date columns (categoryA_date and categoryB_date) based on the Date slicer by using a single measure with TREATAS.
    This way, your slicer from the Date_table will filter both columns at once.

    Try this measure:

    Total Distinct ID =
VAR _filterA =
    CALCULATETABLE (
        VALUES ( 'fact_table'[ID] ),
        TREATAS ( VALUES ( 'Date_table'[Date] ), 'fact_table'[categoryA_date] )
    )
VAR _filterB =
    CALCULATETABLE (
        VALUES ( 'fact_table'[ID] ),
        TREATAS ( VALUES ( 'Date_table'[Date] ), 'fact_table'[categoryB_date] )
    )
RETURN
COUNTROWS ( DISTINCT ( UNION ( _filterA, _filterB ) ) )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    10 months ago

    conniedevina Create a relationship between your date_table and the fact_table for both date columns (categoryA_date and categoryB_date). However, Power BI only allows a single active relationship between two tables at a time. To work around this, you can use DAX functions like TREATAS or USERELATIONSHIP.

     

    Write a DAX measure that checks if either date column falls within the selected date range and then counts the distinct IDs

     

    DAX
    Total Distinct IDs in Date Range =
    VAR SelectedDates = VALUES(date_table[Date])
    RETURN
    CALCULATE(
    DISTINCTCOUNT(fact_table[ID]),
    FILTER(
    fact_table,
    fact_table[categoryA_date] IN SelectedDates
    || fact_table[categoryB_date] IN SelectedDates
    )
    )

  • rohit1991's avatar
    rohit1991
    Super User
    10 months ago

    Hi conniedevina 

     

    You can get the total distinct ID across both date columns (categoryA_date and categoryB_date) based on the Date slicer by using a single measure with TREATAS.
    This way, your slicer from the Date_table will filter both columns at once.

    Try this measure:

    Total Distinct ID =
VAR _filterA =
    CALCULATETABLE (
        VALUES ( 'fact_table'[ID] ),
        TREATAS ( VALUES ( 'Date_table'[Date] ), 'fact_table'[categoryA_date] )
    )
VAR _filterB =
    CALCULATETABLE (
        VALUES ( 'fact_table'[ID] ),
        TREATAS ( VALUES ( 'Date_table'[Date] ), 'fact_table'[categoryB_date] )
    )
RETURN
COUNTROWS ( DISTINCT ( UNION ( _filterA, _filterB ) ) )
  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi conniedevina ,

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by rohit1991  and bhanu_gautam . If you still require support, please let us know, we are happy to assist you.

     

    Thank you.