Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I want to find out the list of unique Serial number that have "B" in Sales Type, and the count of the serial number that fit this conditons. Can anyone please advise how to write the measure/DAX? Thanks in advance.
Transaction Date | Serial Number | Sales Type |
2020-02-09 | C2FE352B | AB |
2020-02-09 | C2FE352B | P |
2020-02-09 | C2FE352B | P |
2020-02-09 | C2FE352B | MB |
2020-02-09 | AB6D3AF3 | B |
2020-02-09 | 925CD7DF | CD |
2020-02-09 | 195B55CA | BSM |
2020-02-09 | 195B55CA | B |
2020-02-09 | 2548D686 | BBB |
2020-02-09 | 2548D686 | B |
2020-02-09 | 2548D686 | BB |
2020-02-09 | 339506B4 | ZC |
2020-02-09 | 339506B4 | C |
The expected result is :
AB6D3AF3 |
2548D686 |
Count = 2
Solved! Go to Solution.
Hi @stephenl ,
Please try this one.
M 2 =
VAR K =
FILTER ( Tab, Tab[Sales Type] IN { "B", "BB", "BBB", "BBBB" } )
VAR rk =
CALCULATETABLE (
VALUES ( Tab[Serial Number] ),
FILTER ( Tab, NOT ( Tab[Sales Type] IN { "B", "BB", "BBB", "BBBB" } ) )
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Tab[Serial Number] ),
FILTER ( Tab, NOT ( Tab[Serial Number] IN rk ) ),
KEEPFILTERS ( K )
)
Hi @stephenl ,
Please try this one.
M 2 =
VAR K =
FILTER ( Tab, Tab[Sales Type] IN { "B", "BB", "BBB", "BBBB" } )
VAR rk =
CALCULATETABLE (
VALUES ( Tab[Serial Number] ),
FILTER ( Tab, NOT ( Tab[Sales Type] IN { "B", "BB", "BBB", "BBBB" } ) )
)
RETURN
CALCULATE (
DISTINCTCOUNT ( Tab[Serial Number] ),
FILTER ( Tab, NOT ( Tab[Serial Number] IN rk ) ),
KEEPFILTERS ( K )
)
Hi both,
Thanks for the prompt resopnse. Sorry that I didn't state my question clear.
I want the serial number that only have sales with "B" in sales type, whether it is one "B' or multiple "B" is fine. I don't want to include any serial number that have other thing. For example : C2FE352B should not be in the list as it has sales types with M and MB.
Thanks in advance.
Hi az38,
Thanks. No error message shows. Can you advise how to show the list of the serial number please? I try to put this new "measured" field in matrix, but not successful.
In addition, do I use "OR" to include "B", "BB", "BBB",...etc in the filter statement please? Any smarter way to do it? Ultimately, I want to have the list of serial number that have sales type with "B"s only, whether "B", "BB","BBB" does not matter. As long as the serial number does not have sales with non "B" in there.
thanks in advance.
Hi @stephenl ,
Let me know whether you want the result like this:
If so, create a control measure like this, put it into the table visual filter and set its value as 1:
Control measure =
VAR _type =
SELECTEDVALUE ( 'Table'[Sales Type] )
RETURN
IF ( LEFT ( _type, 1 ) = "B", 1, 0 )
Create a count measure like @ az38 mentioned to calculate:
Count =
SUMX (
FILTER ( 'Table', LEFT ( 'Table'[Sales Type], 1 ) = "B" ),
IF (
CALCULATE (
DISTINCTCOUNT ( 'Table'[Serial Number] ),
LEFT ( 'Table'[Sales Type], 1 ) = "B"
) = 1,
1,
0
)
)
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Just put it in visual and filter by Sales type like below and create a measure
Measure = calculate(DISTINCTCOUNT('Table'[Serial Number]), ALL('Table'), 'Table'[Sales Type]="B")
Bytheway, you have 3 unique S/N in your data example, not 2
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |