Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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,
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,
@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
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,
working well. Thank you so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
92 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |