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 produ...
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.  

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
)