How to create measure with constant selection for a dimension

[Anonymous]

I know the header looks like weird but i couldn't explain that.
I have a fact and dimension tables as below.

CustomerNo	SalesOrganization	Channel	CreditLimit
1	A	X	500
1	C	Z	500
2	A	Y	200
2	B	Z	200
3	B	Z	50
4	C	X	100
4	A	Z	100

CustomerNo	SalesOrganization	Channel
1	A	X
2	B	Y
3	B	Z
4	C	Z

Two tables were joined on CustomerNo and I'm using SalesOrganization and Channel from dimension table.
I want to create i measure on CreditLimit and it returns;
Sum of CreditLimit but only 1 time per customer (850). (we can assume that a customer has only one credit limit)
IF selected 1 from CustomerNo filter (500)
IF selected A from SalesOrganization filter  (700)
IF selected Z from Channel filter (150)

To summarize, I want it not to add the credit limit for those who have the same customer number

Actually I don't know which dax function should i use. 

I'm waiting for your help

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, but we cannot understand the calculation logic of  “IF selected A from SalesOrganization filter  (700)”, When the SalesOrganization = A, the customer 1 & 2 & 4 counts, so the result should be 800, could you please share the logic why the customer 4 does not count in?

Part of measure for filter SalesOrganization:

Measure = 
IF (
    ISFILTERED ( 'dimension'[Channel] ),
    CALCULATE (
        SUM ( 'fact'[CreditLimit] ),
        FILTER (
            CALCULATETABLE ( 'fact', ALL ( 'dimension' ) ),
            'fact'[Channel] IN DISTINCT ( 'dimension'[Channel] )
                && 'fact'[CustomerNo] IN DISTINCT ( 'dimension'[CustomerNo] )
        )
    ),-1

)

By the way, PBIX file as attached.

Best regards,

[Anonymous]

@v-lid-msft 
I'm sorry for your misunderstanding. Thats my fault. 

Let me tell you the scenerio again.

First of all i was calculate wrong the "IF selected A from SalesOrganization filter  (700)" case 

It should be 500 because i'm using the SalesOrganization filter from dimension table. It is matching with only CustomerNo 1. Then CustomerNo 1 credit limit is 500. 

Actually i just want to give an example for filter selection cases.

I want to create a page which contain all filter fields from dimension table.
Then i want to make selection from filter. If i select a customer it should be return of customer credit limit. If i select SalesOrganization or Channel it should be return sum of the customers credit limit.
For an examples.
If i select customer 1 --> 500
If i select SalesOrg A --> 500, SalesOrg B --> 250, SalesOrg C --> 100, SalesOrg A-B Combine --> 750, SalesOrg A-C Combine 600
If i select Channel X --> 500, Channel Y --> 200, Channel Z --> 150, Channel X-Y Combine --> 700, Channel Y-Z Combine 350

If make no selection from filters 850

I was checked your pbix example. It returns always -1 for CustomerNo and SalesOrganization selections. Thats wrong for my logic. It should calculate CreditLimit for all dimensions.

I added your pbix again with the look i want it to be 
Link

I hope it was descriptive and understandable. Your help is very valuable to me

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirmenet:

Measure = 
SWITCH (
    TRUE (),
    ISFILTERED ( 'dimension'[Channel] ), SUMX (
        DISTINCT ( 'dimension'[Channel] ),
        CALCULATE (
            SUM ( 'fact'[CreditLimit] ),
            FILTER (
                CALCULATETABLE ( 'fact', ALL ( 'dimension' ) ),
                'fact'[Channel] = EARLIER ( 'dimension'[Channel] )
                    && 'fact'[CustomerNo]
                        IN CALCULATETABLE (
                            DISTINCT ( 'dimension'[CustomerNo] ),
                            'dimension'[Channel] = EARLIER ( 'dimension'[Channel] )
                        )
            )
        )
    ),
    ISFILTERED ( 'dimension'[SalesOrganization] ), SUMX (
        DISTINCT ( 'dimension'[SalesOrganization] ),
        CALCULATE (
            SUM ( 'fact'[CreditLimit] ),
            FILTER (
                CALCULATETABLE ( 'fact', ALL ( 'dimension' ) ),
                'fact'[SalesOrganization] = EARLIER ( 'dimension'[SalesOrganization] )
                    && 'fact'[CustomerNo]
                        IN CALCULATETABLE (
                            DISTINCT ( 'dimension'[CustomerNo] ),
                            'dimension'[SalesOrganization] = EARLIER ( 'dimension'[SalesOrganization] )
                        )
            )
        )
    ),
    SUMX (
        DISTINCT ( 'dimension'[CustomerNo] ),
        VAR No = [CustomerNo]
        RETURN
            CALCULATE (
                SUM ( 'fact'[CreditLimit] ),
                FILTER (
                    CALCULATETABLE ( 'fact', ALL ( 'dimension' ) ),
                    'fact'[CustomerNo] = EARLIER ( 'dimension'[CustomerNo] )
                        && 'fact'[SalesOrganization]
                            IN CALCULATETABLE (
                                DISTINCT ( 'dimension'[SalesOrganization] ),
                                'dimension'[CustomerNo] = No
                            )
                )
            )
    )
)

By the way, PBIX file as attached.

Best regards,

[Anonymous]

working well. Thank you so much