Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct Count

Hi guys,

 

I'm having problems with a distinct count measure on card visualization on Power BI. Could you help me please?

 

I have a calculated measure on a matrix and I need to have a card to count the number of occurrences higher than a certain value.

 

Thanks,

Pedro.

Need Help
  • bpsearle's avatar
    bpsearle
    6 years ago

    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

20 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hard to say excalty what is needed with that limited information. Can you provide a sample of your data and what you'd expect the result to be?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

       

      I can give you an example:

       

      *WOI - Weeks of Inventory

       

      storewoi
      a1.5
      b1.7
      c2.3
      d2.5
      e3
      f5

       

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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

         

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    I create measures to refer to measure [current inv] and [avg] in your example.

    assume I have two measure which use columns as fields.

    then use the two measures in another measure to calculate the "woi"

    Measure aveg = SUM(Sheet1[avg])

Measure current inv = SUM(Sheet1[current inv])

Measure woi =
SUMX (
    FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[store] = MAX ( Sheet1[store] ) ),
    [Measure current inv]
)
    / SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[store] = MAX ( Sheet1[store] ) ),
        [Measure aveg]
    )

     

    Best Regards
    Maggie
    Community 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's avatar
      bpsearle
      Resolver II
      6 years ago

      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