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 ID category_a category_b categoryA_date categoryB_date 12345 healthy non-healthy 2024-01-01 2024-03-01 12333 healthy sick 2024-03-01 ...
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 ) ) )
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
)
)