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 have below data set
Customer | Source | SalesAmt |
cust-1 | OMS | 500 |
cust-2 | OMS | 100 |
cust-3 | OMS | 50 |
cust-4 | OMS | 100 |
cust-1 | G5 | 100 |
cust-2 | G5 | 200 |
cust-4 | G5 | 200 |
cust-2 | Onsite | 300 |
cust-4 | Onsite | 100 |
I have a slicer by source when I select OMS i should only show cust-1 data as that is the only customer who has only OMS . If we select OMS and G5 then we should show data for cust-1,cust-2,cust-4. based on selection for source we should only display customers that belong to only those sources.
This is what the out put I am expecting. Please some one can help in writing a dax for this that will be great
Example-1 | |
Select | OMS |
G5 | |
Result | |
cust count | 1 |
Sales Amt | 600 |
Example-2 | |
Select | OMS |
G5 | |
OnSIte | |
Result | |
cust count | 2 |
Sales Amt | 1000 |
Example-1 | |
Select | OMS |
Result | |
cust count | 1 |
Sales Amt | 50 |
Solved! Go to Solution.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Customer count: =
VAR _sourcecount =
COUNTROWS ( DISTINCT ( Source[Source] ) )
VAR _selectedsource =
DISTINCT ( Source[Source] )
VAR _nonselectedsource =
EXCEPT ( ALL ( Source[Source] ), _selectedsource )
VAR _customerstable =
ADDCOLUMNS (
Data,
"@sourcecount", COUNTROWS ( FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) ) )
)
VAR _customerlist =
SUMMARIZE (
FILTER ( _customerstable, [@sourcecount] = _sourcecount ),
Data[Customer]
)
VAR _exceptcustomerlist =
SUMMARIZE (
FILTER ( ALL ( Data ), Data[Source] IN _nonselectedsource ),
Data[Customer]
)
VAR _expectedcustomerlist =
EXCEPT ( _customerlist, _exceptcustomerlist )
RETURN
COUNTROWS ( _expectedcustomerlist )
Customer sales expected result: =
VAR _sourcecount =
COUNTROWS ( DISTINCT ( Source[Source] ) )
VAR _selectedsource =
DISTINCT ( Source[Source] )
VAR _nonselectedsource =
EXCEPT ( ALL ( Source[Source] ), _selectedsource )
VAR _customerstable =
ADDCOLUMNS (
Data,
"@sourcecount", COUNTROWS ( FILTER ( Data, Data[Customer] = EARLIER ( Data[Customer] ) ) )
)
VAR _customerlist =
SUMMARIZE (
FILTER ( _customerstable, [@sourcecount] = _sourcecount ),
Data[Customer]
)
VAR _exceptcustomerlist =
SUMMARIZE (
FILTER ( ALL ( Data ), Data[Source] IN _nonselectedsource ),
Data[Customer]
)
VAR _expectedcustomerlist =
EXCEPT ( _customerlist, _exceptcustomerlist )
RETURN
SUMX (
FILTER ( Data, Data[Customer] IN _expectedcustomerlist ),
Data[SalesAmt]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Please find the url for pbix file.I want to see that data in Matrix..
https://drive.google.com/file/d/1pHHGbbo-3eaNA-zB_Lld8QN9H08qS1ZZ/view?usp=sharing
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Thank you very much for your solution I think when I am displaying in Matrix its not working as intended to.
In this image if I select OMS and G5 matrix should show cust-1 with OMS value of 500 and G5 value of 100.
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |