Forum Discussion

SK87's avatar
SK87
Helper III
4 years ago
Solved

Calculated Column based on two tables

Hi Team, I am facing one problem that is I have two dates "start date" and "End Date". I need to get the count as per below scenario: If I select values <= December'2017 in start date and values >=...
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi， SK87

    If you switch the variable date by slicer, then you should create a measure instead of a calculatted column to count the value. Calculated columns are unaffected by the value selected in the slicer.

    You can create a measure like below and apply it to table visual.

    Count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Start Date] <= SELECTEDVALUE ( 'Calendar Date'[Date] )
            && 'Table'[End Date] >= SELECTEDVALUE ( 'Calendar Date'[Date] )
    )
)

     

    Best Regards,
    Community Support Team _ Eason

SK87's avatar
SK87
Helper III
4 years ago

Thanks amitchandak 

But I need to calculate Count column which you have mentioned in measure as Table[Count]. I had shared above example after calculating in excel but how I can get same count in PBI using date 1 independent variable

v-easonf-msft's avatar
v-easonf-msft
Community Support
4 years ago

Hi， SK87

If you switch the variable date by slicer, then you should create a measure instead of a calculatted column to count the value. Calculated columns are unaffected by the value selected in the slicer.

You can create a measure like below and apply it to table visual.

Count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Start Date] <= SELECTEDVALUE ( 'Calendar Date'[Date] )
            && 'Table'[End Date] >= SELECTEDVALUE ( 'Calendar Date'[Date] )
    )
)

 

Best Regards,
Community Support Team _ Eason