Forum Discussion

Power_Guy's avatar
Power_Guy
Frequent Visitor
1 year ago
Solved

Dax is not working

In this Actual field is  showing 'blank' but instead of showing blank
I wanted to return 0 so to achiveing this i created a dax but it is not working 
Actual = IF(
    Issues[Actual Receipt] = BLANK() || Issues[Actual Receipt] = 0,
    IF(
        Issues[Recommended Distribution] = BLANK(),
        0,
        Issues[Recommended Distribution]
    ),
    Issues[Actual Receipt]
)

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Power_Guy 

     

    Since I don't know what your data looks like, I tested it using simple data. When using the same DAX as yours an error was reported

     

    A single value for column 'Actual Receipt' in table 'Issues' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

     

    this is because this DAX formula handles multiple rows of data without specifying how to aggregate them. Use the SUM function to aggregate the data:

     

    Actual = IF(
    SUM(Issues[Actual Receipt]) = BLANK() || SUM(Issues[Actual Receipt]) = 0,
    IF(
        SUM(Issues[Recommended Distribution]) = BLANK(),
        0,
        SUM(Issues[Recommended Distribution])
    ),
    SUM(Issues[Actual Receipt])
)

     

     

     

    If this is not the problem you are experiencing, please provide some dummy data that maintains the same data structure and tell me how the fields used in your matrix visual are calculated.

     

     Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    1 year ago

    Hello Power_Guy,

     

    Try something like:

     

    Actual = [Actual Receipt] + 0


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

    P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Power_Guy 

     

    Since I don't know what your data looks like, I tested it using simple data. When using the same DAX as yours an error was reported

     

    A single value for column 'Actual Receipt' in table 'Issues' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

     

    this is because this DAX formula handles multiple rows of data without specifying how to aggregate them. Use the SUM function to aggregate the data:

     

    Actual = IF(
    SUM(Issues[Actual Receipt]) = BLANK() || SUM(Issues[Actual Receipt]) = 0,
    IF(
        SUM(Issues[Recommended Distribution]) = BLANK(),
        0,
        SUM(Issues[Recommended Distribution])
    ),
    SUM(Issues[Actual Receipt])
)

     

     

     

    If this is not the problem you are experiencing, please provide some dummy data that maintains the same data structure and tell me how the fields used in your matrix visual are calculated.

     

     Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.