question

Yassir-9592 avatar image
0 Votes"
Yassir-9592 asked CarrinWu-MSFT edited

How to use a Filter with Divide

Hi,

I have a measure that contains a Divide Function like as bellow

 DIVIDE (
             [Selling Price],
             [Cost to Produce]  
            ,
             0
         )

I have a Boolean column in the fact table and i want to add the filter in that measure [Cost to Produce] , i did this update as bellow:

 DIVIDE (
             [Selling Price],
             CALCULATE (
                 [Cost to Produce],
                 FILTER (
                     'Fact Sales',
                     'Fact Sales'[IS_VALID] = 0
                 )
             ),
             0
         )

When i run the report i have a wrong value ,

And Also i have another measure that use Filter in Divide (<numerator> and <denominator>) and return wrong value


  DIVIDE (
                  CALCULATE (
                     [Operate income],
                     FILTER (
                         'Fact Sales',
                         'Fact Sales'[IS_VALID] = 0
                     )
                 ),
                 CALCULATE (
                     [Revenue],
                     FILTER (
                         'Fact Sales',
                         'Fact Sales'[IS_VALID] = 0
                     )
                 ),
                 0
             )

i don't know if my expression is correct or not .

Thank for your help




sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Yassir-9592, could you please do "Accept Answer" for this thread if below anwser help you? By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·

1 Answer

dgosbell avatar image
1 Vote"
dgosbell answered dgosbell commented

So saying "i have a wrong value" gives us no information that we can help you with.

But the simplest way for you to figure this out is to split out the filter expressions into their own measures. Then you can drag the numerator, the denominator and the result of the divide into the one table and see if it's the divide causing the issue (which I doubt) or if one of the two inputs is not what you were expecting.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @dgosbell ,

Thanks for your return.

At the beginning I had this measure

 DIVIDE (
              [Selling Price],
              [Cost to Produce]  
             ,
              0
          )

Then i have a new filter :

 DIVIDE (
              [Selling Price],
              CALCULATE (
                  [Cost to Produce],
                  FILTER (
                      'Fact Sales',
                      'Fact Sales'[IS_VALID] = 0
                  )
              ),
              0
          )

When i display the both Measure i have different value , For me it's false because i need to have the same result because i didn't have 'Fact Sales'[IS_VALID] =1


0 Votes 0 ·

i need to have the same result because i didn't have 'Fact Sales'[IS_VALID] =1

So the rest of the information is a repeat of what you already posted, but this is a new bit of information. Am I correct in assuming then that every row in 'Fact Sales' has [IS_VALID] = 0 so there are no other non-zero values in that column?

If this assumption is correct then what is the expression for your [Cost to Product] measure? Is this only using columns from 'Fact Sales' or is it referencing other tables? If it is referencing other tables what are the relationships between 'Fact Sales' and these other tables?

0 Votes 0 ·