Dax not counting a valid value and returning blank

[reynaldo_malave]

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 Store	Nombre	% $ AsA
1	Store1	-0,11586366
1	Store100	-0,1095688
1	Store 298	-0,1094162
	Store 274	-0,10698009
1	Store 2	-0,10634032
1	Store 12	-0,09827695
1	Store 24	-0,09515624
1	Store 45	0,00355487
1	Store 156	0,00466564
	Store 200	0,00471472
1	Store 25	0,00572713
1	Store 355	0,01080266
	Store 201	0,01114145
1	Store 202	0,01225941

Let me know if you have any ideas.

Thanks

[v-linhuizh-msft]

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]

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]

@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]

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]

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