Unary Operators

[vega]

Hello,

I have a question regarding building a model with unary operators. I have the following dimension:

Metric	MetricAbbrevated	AccountNumber	Order
Room Revenue	RoomRev	10512051	1
Occupancy	Occ		2
ADR	ADR		3
RevPAR	RevPAR		4
Rooms Sold	RS	9105120RS	5
Rooms Available	RA	9105120RA	6

I am going to have a fact table that has a column AccountNumber and a column Amount. I want to use the Metric as a slicer and have one measure. The measure will be the sum of the Amount column in the fact table. As you can see, three of the metrics do not have an account number. I need it so that when the user slices on Occupancy, the model with use unary operators to divide the sum of Rooms Sold and Rooms Available. Slice on ADR gives you a division of the sum of room revenue and rooms sold. Slice on RevPAR gives you a division of room revenue and rooms available. Slicing on the others just gives you the sum of amounts that correspond to the account number.

Does anyone have any tips on how to accomplish this?

[v-jiascu-msft · Accepted solution]

Hi @vega,

Try this demo please. (Many details are in the file.)

Measure =
IF (
    HASONEVALUE ( 'Table1'[Metric] ),
    IF (
        VALUES ( Table1[Metric] ) = "Occupancy",
        DIVIDE (
            CALCULATE (
                SUM ( Table2[Amount] ),
                FILTER (
                    ALL ( Table2 ),
                    'Table2'[AccountNumber] IN { "9105120RS", "9105120RA" }
                ),
                ALL ( Table1 )
            ),
            2,
            0
        ),
        IF (
            VALUES ( Table1[Metric] ) = "ADR",
            DIVIDE (
                CALCULATE (
                    SUM ( Table2[Amount] ),
                    FILTER (
                        ALL ( Table2 ),
                        'Table2'[AccountNumber] IN { "10512051", "9105120RS" }
                    ),
                    ALL ( Table1 )
                ),
                2,
                0
            ),
            IF (
                VALUES ( Table1[Metric] ) = "RevPAR",
                DIVIDE (
                    CALCULATE (
                        SUM ( Table2[Amount] ),
                        FILTER (
                            ALL ( Table2 ),
                            'Table2'[AccountNumber] IN { "10512051", "9105120RA" }
                        ),
                        ALL ( Table1 )
                    ),
                    2,
                    0
                ),
                SUM ( Table2[Amount] )
            )
        )
    ),
    BLANK ()
)

Best Regards,

Dale

[vega]

This gets the job done with some tweaking. Question for you, can you explain why the ALL(Table1 is needed as a filter for the calculate sum?

[v-jiascu-msft]

Hi @vega,

The ALL(Table1) removes the filters from Table1.

Best Regards,

Dale

[parry2k]

yes all this can be done, if you share sample data from both of your tables, will help to provide the solution.