Dax calculations work in excel

Hi,
I am setting up a excel workbook which is similiar to Power BI

The below formula works fine in Power BI but it won’t work in excel. Has anyone experienced this problem?

Deal Group Size $ =
CALCULATE([Funnel (C+U+P)],
FILTER(
VALUES(‘SFDC Details’[IndexSFDC]),
COUNTROWS(
FILTER(
‘Deal Size Bands’,
[Funnel (C+U+P)]>‘Deal Size Bands’[Min Price]
&& [Funnel (C+U+P)]<=‘Deal Size Bands’[Max Price]))
>0))

Hi @KimC,

Without the sample file it is hard to just work with the given DAX code could you please help to upload the sample file. Also screen shots of the error will be very helpful.

But by seeing your code it is evident that you are using VALUES function which is available in Power BI but not in excel that’s why it is not working in excel. You can read below document for more information.

https://docs.microsoft.com/en-us/dax/values-function-dax#:~:text=This%20function%20cannot%20be%20used,filter%20or%20sum%20other%20values.

Thanks
Mukesh

2 Likes

@KimC You are missing a colon before =

In Excel you will have to enter code like this: Deal Group Size $ :=

HI @AntrikshSharma,

Does VALUES function works in excel?

@MK3010 Yup.

https://dax.guide/values/

1 Like

Hi @AntrikshSharma,

I am entering the code as a measure and it doesn’t allow me to add a colon.

image

I don’t get an error message, it says my formula is ok. I don’t however get the right result.

image

@KimC I thought you were having issues with entering the code, As @MK3010 said, without a file it is not possible to tell why your measure is not working, always include a file with your problem.

Hi @AntrikshSharma
I have created an excel file with the Deal Group Size $ measure, which works fine in Power BI but won’t work in Excel, the measure has no errors when you check formula.
image
A file is attached. I appreciate this is excel but I am just applying the same principles as I have learnt for Power BI.
My measure is returning no data

image

any help would be greatly appreciated. Test DNA.xlsx (397.9 KB)

Hi @KimC,

Can you please try using below measure as this gives the output but please validate the output.

Deal Group Size $ :=
VAR Funnel =
    SUMX ( Transform_SFDC, Transform_SFDC[USD] )
RETURN
    CALCULATE (
        Funnel,
        FILTER (
            VALUES ( Transform_SFDC[Opportunity Id] ),
            COUNTROWS (
                FILTER (
                    'Deal Size Bands',
                    Funnel > 'Deal Size Bands'[Min Price]
                        && Funnel <= 'Deal Size Bands'[Min Price]
                )
            ) > 0
        )
    )

Please see below output

Thanks
Mukesh

Hi Mukesh,
I found my error, I used Min Price twice. So I got it to work in my simple model in excel…Yay. Now to get it to work on my complicated model!
Thanks for your help.

HI @KimC,

That’s great, I just now changed it and saw it’s working for you. Below is code and screen shot. Also always try to format the code so it is easy to read and find the error also. You can use https://analysthub.enterprisedna.co/apps/dax to format your code.

Deal Group Size $ :=
VAR Funnel =
    SUMX ( Transform_SFDC, Transform_SFDC[USD] )
RETURN
    CALCULATE (
        Funnel,
        FILTER (
            VALUES ( Transform_SFDC[Opportunity Id] ),
            COUNTROWS (
                FILTER (
                    'Deal Size Bands',
                    Funnel > 'Deal Size Bands'[Min Price]
                        && Funnel <= 'Deal Size Bands'[Max Price]
                )
            ) > 0
        )
    )

Thanks
Mukesh