Lookup Value and return

[gaurav-narchal]

Hello

I need help in creating a measure to valdate/lookup the information on the Invoice level.

If all product types in Invoice number is "True" in Exchange then return "1"

AND

If all product types in Invoice number is "False" in Exchange then return "2"

AND

If product types in Invoice number is "True" or "False" in Exchange then return "3"

Click here to access the sample report.

Thanks

Gaurav

[v-yalanwu-msft · Accepted solution]

Hi @gaurav-narchal  , 

Because your file cannot be opened here, so I create a simple example to calculate the Invoice level ; and create a measure as follows:

level =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Exchange] ),
        ALLEXCEPT ( 'Table', 'Table'[ InvoiceID] )
    )
RETURN
    IF ( _count = 2, 3, IF ( MAX ( [Exchange] ) = "FALSE", 2, 1 ) )

The final output is shown below:

If it still fails to solve your problem, can you re-upload the file?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[Ashish_Mathur]

Hi,

Do you want the result as a calculated column in your table or as a measure in your visual?

[gaurav-narchal]

@Ashish_Mathur  - I would need this as a measure.

[jdbuchanan71]

We can simplify it a bit more even.

Exchange Check = 
VAR _AllRows = COUNTROWS ( 'Table' )
VAR _TrueRows = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Exchange] = TRUE )
RETURN 
SWITCH(
    TRUE(),
    _TrueRows = _AllRows, 1,
    _TrueRows = 0, 2,
    3
)

[jdbuchanan71]

@gaurav-narchal 

Try something like this.

Exchange Check = 
VAR _AllRows = COUNTROWS ( 'Table' )
VAR _TrueRows = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Exchange] = TRUE )
VAR _FalseRows = CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Exchange] = FALSE )
RETURN 
SWITCH(
    TRUE(),
    _TrueRows = _AllRows, 1,
    _FalseRows = _AllRows, 2,
    3
)