Forum Discussion

KarenFingerhut's avatar
KarenFingerhut
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

DAX Calculation Issue from granular table to summary level DAX Statement

Hi there   Im trying to create a DAX statement but am having problems.   The data is at transaction date level giving values of petrol spend per operative. The slicers and dicers at the top are a...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, KarenFingerhut 

     

    I just got this thread today. It's easy to make mistakes with time-intelligence functions so it is not recommended.

    Since you are using slicer, we need to build a context in the card.

    Like this:

    # Flag Spend > 300 Per Month =
COUNTX (
    SUMMARIZE (
        Fuel,
        [InvoiceDate],
        [operative_name],
        "sum", IF ( SUM ( Fuel[GrossValue] ) > 300, 1, 0 )
    ),
    [sum]
)

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,

    Community Support Team _Janey

     

     

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, KarenFingerhut 

 

I just got this thread today. It's easy to make mistakes with time-intelligence functions so it is not recommended.

Since you are using slicer, we need to build a context in the card.

Like this:

# Flag Spend > 300 Per Month =
COUNTX (
    SUMMARIZE (
        Fuel,
        [InvoiceDate],
        [operative_name],
        "sum", IF ( SUM ( Fuel[GrossValue] ) > 300, 1, 0 )
    ),
    [sum]
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

 

Best Regards,

Community Support Team _Janey

 

 

  • KarenFingerhut's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Janey

    I've learnt something new which will really help me with future dashbaords. Thank you 🙂

    Kind regards

    Karen

    • v-janeyg-msft's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      KarenFingerhut 

       

      Thanks for your recognition, glad you can modify the code yourself, you are smart 🤝.

       

      Best Regards,

      Community Support Team _Janey

       

  • KarenFingerhut's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Hi Janey

     

    Thanks for getting back to me and apologies for the late reply.

     

    Unfortunately it dont work. The results it was returning were too high when checking against the raw data. I have subsequently come up wth a workaround where I insert another dataset and link them together. However would be good to know how to do this as an additional dataset isnt idea.

    Thank you

    Kind regards

    Karen

  • KarenFingerhut's avatar
    KarenFingerhut
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Hi Janey

     

    If I change COUNTX to SUMX it works a treat 🙂

     

    Thank you

    K