Dax calculations work in excel

KimC · November 21, 2020, 7:05am

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

MK3010 · November 21, 2020, 7:30am

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

AntrikshSharma · November 21, 2020, 8:37am

@KimC You are missing a colon before =

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

MK3010 · November 21, 2020, 9:37am

HI @AntrikshSharma,

Does VALUES function works in excel?

AntrikshSharma · November 21, 2020, 9:47am

@MK3010 Yup.

KimC · November 21, 2020, 10:19am

Hi @AntrikshSharma,

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

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

AntrikshSharma · November 21, 2020, 10:50am

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

KimC · November 22, 2020, 2:12am

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.

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

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

MK3010 · November 22, 2020, 4:13am

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

KimC · November 22, 2020, 4:25am

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.

MK3010 · November 22, 2020, 4:40am

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