count number of bungalows considering specific dates

[jvelling]

Hi everyone,

I'm trying to calculate the number of active bungalows on a specific date. I have got a date table and a bungalow table, which have a one (date table) to many (bungalow table) relationship. Let's say I would like to show the number of available/active  bungalows at 05 January 2021 (slicer filter) in a matrix table or graph. I've tried a couple of DAX formulas by using count and if statements, but neither do really work out for me yet.

Date table: 

Date

01-01-2017

02-01-2017

31-12-2021

Bungalow table

Code	Sort	Type	Removed
1	Big	Farm	31-12-2019
2	Small	Prisma 4	02-01-2021
3	Small	Prisma 2	31-12-2021
4	Small	Unit 2	04-02-2018
5	Decent	Prisma 6	21-02-2020

A lot of thanks in advance, and I am looking forward to the solution!

[MFelix · Accepted solution]

Hi @jvelling 

If you have a realtionship between your tables try the following code:

count =
CALCULATE (
    COUNT ( 'Table'[Code] );
    FILTER (
        ALLSELECTED ( 'Table'[Removed] );
        'Table'[Removed] >= MAX ( 'Calendar'[Date] )
    );
    CROSSFILTER ( 'Calendar'[Date]; 'Table'[Removed]; NONE )
)

[v-jingzhang]

Hi @jvelling 

Sorry can you describe how to know whether a bungalow is active or not? Does Removed column mean that a bungalow is active or inactive after that date? We need more info to understand your expected result and create a measure for it. Thanks.

Regards,

Jing

[jvelling]

Hi Jing,

Thank you for your message. 

Removed indeed refers to inactive from the selected date. The DAX code of MFelix does work in this situation. 

Kind regards,

Jvelling

[MFelix · Accepted solution]

Hi @jvelling 

If you have a realtionship between your tables try the following code:

count =
CALCULATE (
    COUNT ( 'Table'[Code] );
    FILTER (
        ALLSELECTED ( 'Table'[Removed] );
        'Table'[Removed] >= MAX ( 'Calendar'[Date] )
    );
    CROSSFILTER ( 'Calendar'[Date]; 'Table'[Removed]; NONE )
)

[jvelling]

Hi Miguel,

Thank you for your quick reply. YOur DAX Code does indeed work. Thank you very much!!

How does the Crossfilter function contribute to the code? Without cross filtering it does not work properly. 

Thanks a lot again and I look forward to hearing more about the cross filtering option. 

Kind regards,

Vellinga

[MFelix · Accepted solution]

Hi @jvelling ,

The cross filter function specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns, meaning that it makes the filtering selection.

For this function yoiu have 3 options (single, both and none). Usually on a one to many relationship the cross filtering is set to single going from the one side to the many, so you can filter the table on the many side from the one side table but not the other way around.

In your case what you need is to turn off that filtering so I have used the None option meaning that I have turn off the filtering between both tables that is the same has deleting the relationship between the tables so you can keep using your relationship for the other visualizations but on the specific case of this measure there is no relationship.

Check the description of the CROSSFILTER function on the link below:

https://dax.guide/crossfilter/

[jvelling]

Thank you Miguel!!