Forum Discussion

reynaldo_malave's avatar
reynaldo_malave
Icon for Helper III rankHelper III
1 year ago

Dax not counting a valid value and returning blank

Hi guys.

 

I am working on a dax measure and cant find the problem here. for some reason dax is not recognizing some valid values and it returns blanks. What am trying to do is create a table and the countrows after applying some filters. I have tried a few variations of this measure but nothing seems to work.

here is the measure

# growth < 5% =

VAR StoreSummary =

    SUMMARIZE(
        VALUES(Store[UntiNumber]),
        Store[UntiNumber],
        "ASA", [% $ AsA]
    )

RETURN

    CALCULATE(
        countrows( Store),
        Farmacias[SameYear] = true,
        FILTER(
            StoreSummary, [ASA] <= 0.05
        )
    )

here is an extract of the answer. It returns some blank values 

Count of StoreNombre% $ AsA
1Store1-0,11586366
1Store100-0,1095688
1Store 298-0,1094162
 Store 274-0,10698009
1Store 2-0,10634032
1Store 12-0,09827695
1Store 24-0,09515624
1Store 450,00355487
1Store 1560,00466564
 Store 2000,00471472
1Store 250,00572713
1Store 3550,01080266
 Store 2010,01114145
1Store 2020,01225941

 

Let me know if you have any ideas.

 

Thanks

Need Help
Show and Tell
Tutorial Requests

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi reynaldo_malave - can you create below calculation. 

     

    First findout Total % $ AsA using a measure as below:

    Total % $ AsA = SUM(Blkdata[% $ AsA])

     

    create another measure that may resolve the blank values as below: [ Please replace the table name as per your model]

     

    # Growth < 5% =
    VAR StoreSummary =
        FILTER(
            ADDCOLUMNS(
                VALUES(Blkdata[UntiNumber]),
                "ASA", [Total % $ AsA]
            ),
            [Total % $ AsA] <= 0.05
        )

    RETURN
        CALCULATE(
            COUNTROWS(StoreSummary),
            Blkdata[SameYear] = TRUE()
        )
     

     

     

    Hope this works

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    reynaldo_malave 

    Revised Measure

    # growth < 5% =
    VAR StoreSummary =
    SUMMARIZE(
    FILTER(
    VALUES(Store[UntiNumber]),
    Farmacias[SameYear] = TRUE // Ensure filtering on SameYear
    ),
    Store[UntiNumber],
    "ASA", [% $ AsA]
    )

    RETURN
    COUNTROWS(
    FILTER(
    StoreSummary,
    [ASA] <= 0.05 && NOT(ISBLANK([ASA])) // Ensure ASA is not blank
    )
    )

    💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

     

    • reynaldo_malave's avatar
      reynaldo_malave
      Icon for Helper III rankHelper III
      1 year ago

      Hi Kedar,

       

      Thanks for your answer. I tried your measure but it keeps returning the same result. Blanks for values that should not be blank or better described fall within the range of nonblank values. It really is a strange behavior.

       

      Thanks,

       

      Reynaldo

  • Angith_Nair's avatar
    Angith_Nair
    Icon for Continued Contributor rankContinued Contributor
    1 year ago

    Hi reynaldo_malave 

     

    Use this DAX

    # growth < 5% = 
VAR StoreSummary = 
    ADDCOLUMNS(
        VALUES(Store[UntiNumber]),
        "ASA", [% $ AsA]
    )

RETURN 
    CALCULATE(
        COUNTROWS(Store),
        FILTER(
            StoreSummary,
            [ASA] <= 0.05 && NOT(ISBLANK([ASA]))
        ),
        Farmacias[SameYear] = true
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the replies from rajendraongole1, Kedar_Pande and Angith_Nair.

     

    Hi reynaldo_malave ,

     

    What I need to check with you is whether there is a relationship between the Farmacias table and the Store table, and whether the Farmacias[SameYear] corresponding to the stores that return a null value is not equal to true and therefore not calculating a valid value.

     

    If the problem persists, can you provide a link to the .pbix file that removes the sensitive data, this will help the problem to be resolved the fastest!

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.