Using Userelationship in Allselected

[jagan2678]

This is my measure, 

Include all selections? =

VAR _selected =

    ALLSELECTED ( 'dim'[year] )

VAR _options =

    CALCULATE (

        DISTINCTCOUNT ( 'hotel'[year]  ),

        FILTER (

            ALLSELECTED ( 'hotel' ),

            'hotel'[hotel] = MAX ( 'hotel'[hotel] )

                && 'hotel'[year] IN _sel

        )

    )

 

RETURN

    IF ( COUNTROWS ( _selected ) = _options, 1, 0 )

 

The data model is 

Column year of the office table is already in an active relationship with colum year of the dimdate table.

Here im trying to use the dim[year] which is an inactive one to many relation with hotel[year]. But since its in an inactive relation im not able to compare the output of allselected of dim[year] with the hotel[year] as in the measure. let me know how to solve this.

[Anonymous]

Hi @jagan2678 ,

I create a sample to have a test by your code.

Hotel:

Dim:

Relationship:

Result is as below.

If this is not the solution you need, please show me a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jagan2678]

this is my data model.. so as per this, i already have an active relationship between year column of office table with dimdate table..

so my challeneg is to use my measure which was created to perform AND logic in slicers to use an inactive relationship in the allselected function.

[Anonymous]

Hi @jagan2678 ,

According to your screenshot, I think there are an active relationship between DimDate and hotel. So when you filter on DimDate slicer, the relationship will impact the columns from hotel. I suggest you to create a new unrelated DimDate2 table to achieve your goal.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jagan2678]

sorry there was a correction in my data model. i have updated the data model correctly now. can u suggest for this.

[amitchandak]

@jagan2678 , first have a measure with userelationship and then use allselected

CALCULATE( CALCULATE(SUM(Table[Value]),USERELATIONSHIP('Table'[Date],[Date])),allselected(Table))