Forum Discussion
Distinct Count
Hi Pedro
Start afresh and ignore the previous DAX measures for WOI < 2. Add 2 new measures:
WOI < 2 (hidden) = if([WOI (8W)] > 0 && [WOI (8W)] < 2,1,0)
WOI < 2 = SUMX ( VALUES ( SO_INV_LOJA[Nome da Loja] ), CALCULATE ( [WOI < 2 (hidden)] ) )
For some reason I can't add a screen shot so will try doing that in another reply.
The hidden measure calculates WOI but only in the context where you have the Nome da Loja. If you look at the total it shows as zero because the total WOIis 8.51.
The visible WOI < 2 measure shows the total correctly because it is forcing the calculation to be applied at a grouped level.
I found the answer here: https://brentgreenwood.blogspot.com/2012/12/dax-groupers-summarize-and-aggxvalues.html
Thanks
Brian
screen shot:
Hi,
I can give you an example:
*WOI - Weeks of Inventory
| store | woi |
| a | 1.5 |
| b | 1.7 |
| c | 2.3 |
| d | 2.5 |
| e | 3 |
| f | 5 |
I need to create a conditional/dax on a card that shows me this:
| WOI >2 | WOI 2-4 | WOI<2 | ||
| 2 | 3 | 1 |
How should I do this distinct counting considering that my WOI field is a calculated measure?
Thanks.,
Pedro.
Are those displayed results be what you would expect from the sample data you displayed?
I believe the expected results are shown below
| WOI >2 | WOI 2-4 | WOI<2 | ||
| 4 | 3 | 2 |
Oh, sorry.. the right answer is: (like the colors)
store woi a 1.5 b 1.7 c 2.3 d 2.5 e 3 f 5 WOI >2 WOI 2-4 WOI<4 2 3 1 Im using this DAX, but the result is wrong:
teste = CALCULATE(DISTINCTCOUNT(SO_INV_LOJA[Nome da Loja]);FILTER(SO_INV_LOJA;[WOI (8W)]<2))Anonymous You have your greater than > and less than < symbols the wrong way round which is why v-juanli-msft is confused. His interpretation of what the results should look like is correct based on how you present the <> symbols
Hi Ross_PBI and v-juanli-msft , you guys are right. I switched the symbols.
Could you help me based on my answer to bpsearle ? I exemplified better what I am trying to do.
Thank u so much.
Pedro.
v-juanli-msftCommunity Support
Hi Anonymous
I don't understand your calculation?
Based on my understanding, the values satisfy the condition as below
store woi WOI >2 WOI 2-4 WOI<4 a 1.5 Y b 1.7 Y c 2.3 Y Y Y d 2.5 Y Y Y e 3 Y Y Y f 5 Y Please clear me so we can move to next step.Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- bpsearle
Resolver II
There are different ways of doing this depending on how much data you have and that effects performance, or what you want to do with visuals.
You can either add a new calculated column to the table that reflects the categories so would have the following values “WOI < 2”, “WOI 2-4” and “WOI > 4”. The calculation would be a set of nested Ifs. Then have a measure that does a COUNTROWS on the table. In the visual you use the new category and new measure.
The other way of doing this is to create a new measure for each of the categories. The “WOI < 2” measure would be:
WOI < 2 = calculate(COUNTROWS( FILTER(sheet1,Sheet1[WOI]<2)))
Note the measure above can be achieved with an IF statement but best practice is to use FILTER as this is much faster. For small datasets if may not matter but larger datasets it will
Hope this helps, any questions just shout!
