Text measure

[luigui]

Hi,

I have 3 tables in a simple model:

1) Customer dimension:

Customer Id	Customer Name
C1	        Customer 1
C2	        Customer 2
C3	        Customer 3
C4	        Customer 4

2) Fact 1 with F1 measure by customer

Customer Id	Value F1
C1	        100
C2	        200

3) Fact 2 with F2 measure by customer

Customer Id	Value F2
C2	        1000
C3	        2000

Fact 1 and Fact 2 tables have relationship with customer by "Customer Id" column.

If a create a table visualization with Customer Name and measures "Value F1" and "Value F2", the results are combined ok:

Customer Name	Value F1    Value F2
Customer 1	100
Customer 2	200	    1000
Customer 3		    2000

Now I want a new measure that "flags" the records from the above visualization with value in F1 and F2 measures. I create the measure "Measure_Ok" like this:

Measure_Ok = IF(SUM(F1[Value F1]) <> BLANK() && SUM(F2[Value F2]) <> BLANK(); "Ok"; "Not ok")

If I add to the table visualization, it works ok, but "magically" it adds a new record for "Customer 4"

Customer Name	Value F1    Value F2	Measure_Ok   
Customer 1	100			Not ok
Customer 2	200	    1000	Ok
Customer 3		    2000	Not ok
Customer 4			        Not ok

Why it adds this Customer 4, and how can I remove it?

Thanks!

[MFelix · Accepted solution]

Hi @luigui,

Measures are based on context, since your are making the table based on the customer table it will return all the values from that table and then calculate the measure you created in this case returns "NOT OK" result.

Redo your measure to this:

Measure_Ok =
IF (
    SUM ( F1[Value F1] ) = BLANK ()
        && SUM ( F2[Value F2] ) = BLANK ();
    BLANK ();
    IF (
        SUM ( F1[Value F1] ) <> BLANK ()
            && SUM ( F2[Value F2] ) <> BLANK ();
        "Ok";
        "Not ok"
    )
)

Should remove the C4 customer.

Regards,

MFelix