Dynamic Cluster with DAX (Power BI)

Question

I have following data model:

Table: Contract

Table: Rooms

Table: Cluster

& table: Date (standard calendar table)

My fact table is Contract, which has relation to Rooms via ContractID.

What I need to do is following:

select yyyy-mm on date slicer in PBI (single selection)
read out selected value - last day of selected month: CALCULATE ( MAX ( Date[Date] ), ALLSELECTED ( Datum ) )
show all valid Contracts based on date selection (previous step)
additionally show Amount of rooms based on date selection
show also RangeDesc from table Cluster based on date selection

Example:

selected value in date slicer: 2020-12
contract valid = Yes
Amount of rooms: 5 ; Desc: Medium

new selected value: 2021-02

contract valid = Yes
Amount of rooms: 6 ; Desc: Large

Could you please help me with DAX?

Thanks!

smpa01 smpa01 · Accepted Answer · 2021-09-28T13:40:41

You can achieve the desire result with following measures

_maxVal = 
VAR _selectedMaxDatebyYr_Mo =
    CALCULATE (
        MAX ( 'Calendar'[Calendar_Date] ),
        ALLSELECTED ( 'Calendar'[YR-MO] )
    )
VAR _maxRoomAmount =
    CALCULATE (
        MAX ( Rooms[Amount] ),
        FILTER ( Rooms, Rooms[ValidFrom] <= _selectedMaxDatebyYr_Mo && Rooms[ValidTo] >= _selectedMaxDatebyYr_Mo )
    )
RETURN
    _maxRoomAmount

_rangeDesc = 
SWITCH (
    TRUE (),
    [_maxVal] == BLANK (), BLANK (),
    CALCULATE (
        MAX ( 'Cluster'[RangeDesc] ),
        FILTER (
            'Cluster',
            'Cluster'[RangeFrom] <= [_maxVal]
                && 'Cluster'[RangeTo] >= [_maxVal]
        )
    )
)
_isValid = SWITCH(TRUE(),[_maxVal]==BLANK(),BLANK(),"Yes")

Minimum dependency - a Calendar Table with following structure

| Calendar_Date | YR-MO  |
|---------------|--------|
| 2000-01-01    | 2000-1 |

Dynamic Cluster with DAX (Power BI)

1 Answers