From PowerBi to PowerPivot

[marsclone]

I have a formula in PowerBi that I would like to use in PowerPivot.

Only in PowerPivot, selectedvalue is not available and I think I should use if(hasonevalue)?
However, I get the message:

In the Switch function, comparing values of type True/False with values of type Number is not supported. Consider using the Value or Format function to convert one of the values.

How should I modify the formula?

Thanks!

PowerBi formula:

Total IST c =

if(AND([IfFiltered]=true(),[CheckHoofd?]=1),

BLANK(),

SWITCH(TRUE(),

SELECTEDVALUE(Hoofd[Subtotal?])=0,[Total IST a],

SELECTEDVALUE(Hoofd[Subtotal?])=1,[Total IST b]))

PowerPivot formula:

Total IST c:=if(AND([IfFiltered]=true();[CheckHoofd?]=1);
BLANK();
SWITCH(TRUE();
IF(HASONEVALUE(Hoofd[Subtotal?]);0;[Total IST a]);
IF(HASONEVALUE(Hoofd[Subtotal?]);1;[Total IST b])
))

[v-yingjl]

Hi @marsclone ,

Besides the equivalent expression, if you don't need to return an alternateResult,you can often just use MAX instead of SELECTEDVALUE when there is more than one value to choose from.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

What data types are your [IfFiltered] and [CheckHoofd?] measures?  In any case, can you compare them to 0 or 1 instead of FALSE() or TRUE()?  Or you can wrap your measure in INT( ) to return 1 or 0 from a T/F measure.

Pat

[marsclone]

@mahoneypat 

Hi Pat,

these are the measures i use, the IfFiltered is looking at text, the other ones to numbers. Number 5 goes wrong.

Thank you in advance!

1. CheckHoofd?:=Max(Hoofd[Subtotal?])

2. IfFiltered:=ISFILTERED(Grootboek[Ertragslage])

3. Total IST a:=sum('Grootboek 2'[Bedrag in EV])

4. Total IST b:=if(COUNTROWS(Hoofd)=1;
CALCULATE([Total IST a];
all(Hoofd);
Hoofd[Hoofd ID]<=VALUES(Hoofd[Hoofd ID])
)
)

5. Total IST c:=if(AND([IfFiltered]=true();[CheckHoofd?]=1);
BLANK();
SWITCH(TRUE();
IF(HASONEVALUE(Hoofd[Subtotal?]);0;[Total IST a]);
IF(HASONEVALUE(Hoofd[Subtotal?]);1;[Total IST b])
))

[edhans · Accepted solution]

See the DAX documentation here - SELECTEDVALUE function - DAX | Microsoft Docs

Your 2nd parameter needs to be the field wrapped in VALUES()