Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
reynaldo_malave
Helper III
Helper III

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

5 REPLIES 5
v-linhuizh-msft
Community Support
Community Support

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.

Angith_Nair
Continued Contributor
Continued Contributor

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
    )
Kedar_Pande
Community Champion
Community Champion

@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

 

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

rajendraongole1
Super User
Super User

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()
    )
 

 

rajendraongole1_0-1730151865465.png

 

Hope this works

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.