Forum Discussion

gaurav-narchal's avatar
gaurav-narchal
Helper I
5 years ago
Solved

Lookup Value and return

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

Need Help
Show and Tell
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    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.  

5 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    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
)

     

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    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
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

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

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    5 years ago

    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.