topic Re: Using Variables as filters in DAX Commands and Tips

Using Variables as filters

ChristianHE — Wed, 30 Sep 2020 12:32:29 GMT

Hi community

I am having a little trouble understanding how to use variables, i have made the following formula:

Test =

Var Rep = VALUES(Kundetabel[Repræsentant])

Var Kunde = VALUES(Kundetabel[KUNDENRNAVN])

return

CALCULATE([Potientiale];

TOPN(10;

CALCULATETABLE(all(Kundetabel);

FILTER(Kundetabel; Kundetabel[Repræsentant] in Rep)

)

;[Potientiale];ASC)

;Rep; Kunde

)

I was hoping my calculatetable would give me a table consisting only of the chosen "Repræsentant", but i suspect it does not. Can someone explain me how it works, alternately see what is wrong with my formula?

Re: Using Variables as filters

amitchandak — Wed, 30 Sep 2020 13:13:05 GMT

ChristianHE , Try like

Test = CALCULATE([Potientiale];TOPN(10,allselected(Kundetabel);[Potientiale],ASC),VALUES(Kundetabel[KUNDENRNAVN]))

Re: Using Variables as filters

ChristianHE — Thu, 01 Oct 2020 11:49:14 GMT

amitchandak it works, Thank you very much.

If i wanted to add more filters to my [Kundetabel], for example if i only wanted to see customers with a sales above 1. Would that be possible to add in the formula or should it be added in the "Filters"-pane?

Re: Using Variables as filters

amitchandak — Thu, 01 Oct 2020 13:29:22 GMT

ChristianHE , better have to measure with filter sales >1, if it line level. If it after grouping then use values

sumx(Filter(values(Table[Col1]),[sales]>1),[Sales])